I've recently been doing a performance analysis on a F5 web property. If you haven't seen my article on measuring DotNetNuke Website Performance, check it out now!
When a DotNetNuke website is not performing adequately, the first tool I use (as a developer) is the Microsoft SQL Server Profiler. This tool monitors database traffic allowing you to pinpoint potential bottlenecks in DotNetNuke that are database oriented.
Setting up the SQL Trace
When creating a new trace for tuning, make sure to use the tuning template:
And select these events in the events selection tab:
Importantly, add in these filters by clicking the column filters button:
Database Name : the name of your DotNetNuke database
Duration > 500ms : queries running longer than 500ms
Selecting a Query to Analyze
If your site is performing slow, you should begin to see entries populated (providing someone is hitting your website, if not, click a few pages on to generate some database traffic).
In my case, the first query that popped up had a duration of 1205ms and was:
exec dbo.activesocial_Util_GetUserId @PortalId=0,@DisplayName=N'scott mcculloch'
This is not great performance for a sql query.
Now, to resolve this issue, you'll have two choices:
#1 Contact the Module Vendor
It appears on face value to be fairly simple query. We're not pulling back a ton of data, we're just getting a UserID right? So if you are an administrator (and not a developer), it might be best to contact the module vendor with the result of your profile trace.
The module vendor might want context for the query. This context for this query might include how many users do you have in the database? How much traffic does your DotNetNuke website get? These are very helpful to any module developer.
#2 Look into the problem yourself
If you are a developer (or as in the case of active social, a module no longer supported by the vendor), you might want to crack open the problem yourself!
In Microsoft SQL Server Management Studio, open up a new query and paste in the query from the earlier SQL trace. For me it is:
exec dbo.activesocial_Util_GetUserId @PortalId=0,@DisplayName=N'scott mcculloch'
Before you run it, right click on the pane to select Include Actual Execution Plan and Include Client Statistics.
Both of these settings will give us more information about the query when it is run.
If you run it by pushing run query or pressing f5. You'll notice 2 extra tabs appear (Client Statistics and Execution Plan). We know previously that this query takes 1.2 seconds to run, but let's take a look at how long it will take via the Client Statistics tab.
Looking at the Total Execution Time supports our previous query time from the SQL Server Profiler.
So let's take a look at the Execution Plan (click to expand):
It looks like the table scan is attributable for 95% and the sort for the other 5%.
A few things jump out:
- [5%] Why do we need to order anything if you are only returning 1 record?
- [95%] It is scanning the table manually, there is no index setup for this query to use.
The easiest thing to target without affecting the module is to setup an index. This shouldn't affect the module running at all as it is only a reference list of data.
If I look further at the execution plan, SQL Server is actually telling me what index I am missing!
You should be able to right click on the green text and select "Missing Index Details..":
This will copy the missing index details to a new query window. You will have to give it a name before running it. Here is what mine looks like:
CREATE NONCLUSTERED INDEX [IX_AS_User_PortalId]
ON [dbo].[activesocial_User] ([PortalId])
INCLUDE ([UserId],[VanityName])
Once you have created the index, re-run the query again and look at the execution plan:
Via the execution plan, we can see that it's at least using the index now.
Has this made a difference to our execution time?
Great Success! It has now reduced this part of the query!
Update: After further investigation, this stored procedure was performing even slower in a particular case. If it didn't find a match, you would be pushed down into a section where it did this query:
SELECT TOP 1 IsNull(UserId,-1) from dbo.Users WHERE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(lower(DisplayName),' ','-'),'.','-'),'---','-'),'--','-'),'''',''),'_','') = lower(@DisplayName) ORDER BY UserID
This is particularly bad since it scans the entire table replacing the values of "DisplayName" a total of 7 times (one for each replace and the lower). The more users you have, the worse performing this is going to get!
Conclusion
Throughout this article you've seen how the SQL Profiler can be used to view database activity on your DotNetNuke website.
You've seen how it's possible to look at non-performing queries and approaches to handling them.
If you can at least identify the bottlenecks in your website and share this information with module developers, it will help to improve the overall performance of your website.