Identifying and Isolating SQL Server performance issues

Support for Micosoft SQL Server in the context of Jiwa installations.

Identifying and Isolating SQL Server performance issues

Postby Mike.Sheen » Thu May 21, 2009 11:17 am

This post will enable you to measure SQL performance and identify what the slowest queries are, and perhaps give you a starting point for some index optimisation and tuning to increase performance.

The best way to measure performance is to record the time taken for each query to execute during the actions where performance is a concern.

To do this, we'll capture the SQL to a trace file, import the data into a SQL table, and then run a query to display the slowest queries.

Step 1.

Open SQL Server Profiler, and start a new trace. We want to check the "Save to file" checkbox, and nominate a filename and location, and we want to uncheck "Enable file rollover". Enable file rollover causes profiler to produce multiple files, which is fine but complicates the import process later. For this exercise we want just a single file.

Image

On the Events Selection tab, check "Show all columns", then scroll across and find the "DatabaseName" column and check each checkbox in the DatabaseName column, as shown below :
Image

Now press the "Column Filters" button to bring up the Edit Filter dialog. We want to add a filter to just capture the queries for our database - so select the DatabaseName node in the left and in the treeview on the right, expand the "Like" node and key in the database name, as shown below :
Image

Now press the Run button, and the trace will begin logging to a file.

Now, get some users into Jiwa and start performing actions which you want to measure - ie: stuff they reckon is slow.

Step 2.

Now that we have the data in a trace file, we want to import the data into a SQL Table. You can get profiler to capture traces to a SQL table, but that may poison your trace, as SQL would be using resources to log the trace to a table.

Open Query Analyser, and in the master database run the following query :

Code: Select all
SELECT * INTO JIWASQLTrace FROM ::fn_trace_gettable('C:\Users\mikes\Documents\JIWATrace.trc', default)


Replace 'C:\Users\mikes\Documents\JIWATrace.trc' with the path and filename you chose when you began the trace.

If the above query returns an error along the lines of "either does not exist or is not a recognizable trace file. Or there was an error opening the file.", then ensure that the SQL Service has READ access to the trace file - locate the file, right click on the file, choose security, etc and grant read access to SQL Services.

Once run, the above query would have created the table JIWASQLTrace and imported the data into it.

Step 3.

Analyse the data. probably the most useful query is to show the slowest queries, and the query below does just that :

Code: Select all
SELECT TextData, ApplicationName, LoginName, Duration, CAST((CAST(Duration AS DECIMAL(19,3)) / 1000000) AS DECIMAL(19,3)) [Duration (Seconds)], starttime, endtime, Reads, Writes, CPU
FROM JIWASQLTrace
WHERE COALESCE(CAST(TextData AS VARCHAR(Max)), 'NULL') <> 'NULL'
ORDER BY Duration DESC


The TextData column shows the query, the topmost rows the slowest.

Armed with this information, you should now be able to start examining indexes, reports, etc and begin to tune for better performance.

I've deliberately included logging for applications other than Jiwa as well, as external systems hitting the database will affect performance, and would benefit from similar scrutiny.

The SQL user will help you identify the source of the query - by default all reports use the 'JiwaReports' SQL user, and the application uses the 'JiwaUser' user.

If you want more granular information as to which particular user(s) are loading the system, then you should create separate SQL users (with the appropriate permissions) and setup their staff accounts in Jiwa to use those SQL logins. Then you'll be able to see not only which queries are performing badly, but also which users were running them (and perhaps this well help to identify what they were doing at the time).
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Identifying and Isolating SQL Server performance issues

Postby Mike.Sheen » Thu Jul 29, 2010 9:51 pm

Mike.Sheen wrote:This post will enable you to measure SQL performance and identify what the slowest queries are, and perhaps give you a starting point for some index optimisation and tuning to increase performance.


Some people have asked why they would need to do any sort of index tuning and why Jiwa does not do this automatically.

Put simply - Every database is different and if we attempted to dictate the indexes you must have for your data set, then it would most likely be wrong.

We cannot determine the optimal tuning without examining your daily workloads. Consult your Jiwa dealer for such information.
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Identifying and Isolating SQL Server performance issues

Postby pricerc » Tue Aug 03, 2010 12:43 pm

I've recently discovered that SQL 2008 has some extra tools for assisting with index optimisation.

In SQL Server Management Studio (SSMS), open a new query window, and paste in a problem query from your trace.

Then enable the 'Include Actual Execution Plan' from the menu and run your query.

Run your query. SSMS will return the results, and there will be a tab called 'Execution Plan'. If you look at this tab, it *may* also include a message in green about a possible index that *may* improve your performance. If you right-click this message, there's a menu option 'Missing Index Details'. Click this, and SSMS will create a new Query window with the SQL needed to generate the 'missing' index, in the form of a template that you need to edit (you need to give the index a name) before executing it.

For example, I created this completely contrived query against the JiwaDemo database:
Code: Select all
SELECT     M.InvoiceNo, M.InvoiceInitDate, M.OrderNo, M.InvoiceTotal, L.LineNum, L.PartNo, L.Description, L.QuantityOrdered
FROM         SO_Main AS M INNER JOIN
                      SO_History AS H ON M.CurrentHistoryNo = H.HistoryNo AND M.InvoiceID = H.InvoiceID INNER JOIN
                      SO_Lines AS L ON H.InvoiceHistoryID = L.InvoiceHistoryID
WHERE     (M.InvoiceTotal > 10000) AND (L.QuantityOrdered > 10)


The query results looked like this:
Index Hint.png
SSMS screen shot
Index Hint.png (84.97 KiB) Viewed 8257 times


And the recommended index SQL like this:
Code: Select all
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[SO_Lines] ([QuantityOrdered])
INCLUDE ([InvoiceHistoryID],[LineNum],[PartNo],[Description])


While these 'hints' may not be perfect, they're a really good place to start. I've had a few problem queries where creating the indexes recommended by SSMS has made a big difference to performance. You may need to run the query several times, added recommended indexes, some queries need more than one index, and adding an index can 'move' the bottleneck and identify a new place for improvement.

regards,
Ryan
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Identifying and Isolating SQL Server performance issues

Postby Mike.Sheen » Sun Aug 08, 2010 8:36 pm

pricerc wrote:I've recently discovered that SQL 2008 has some extra tools for assisting with index optimisation.

In SQL Server Management Studio (SSMS), open a new query window, and paste in a problem query from your trace.

Then enable the 'Include Actual Execution Plan' from the menu and run your query.


Ryan,

Yep - actually I think they've been there since SQL 2000, just a little hidden :)

And yes, they are a great way to identify the parts of queries which are lacking performance.

A caveat, though - I've used this and tried to introduce indexes based on the results and had somewhat mixed results. The internal statistics of MSSQL is quite fickle and adding an index may have no or detrimental results until the statistics are rebuilt - which would require (in a production environment) a few days of use.

But anyway - thanks for posting this - it's useful information!
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Identifying and Isolating SQL Server performance issues

Postby pricerc » Mon Aug 09, 2010 6:46 am

Mike,

the 'actual execution plan' stuff has been there for ages, but I'd never noticed the convenient 'right-click and generate create index script' bit, which I think is a function of SSMS 2008.

I know results may vary, which is why I said the indexes *may* help; they're just a starter for 10 if you're working on a specific query. I've had them make a big difference, and I've had them make negligible difference. Although I've never had it make things worse.

/Ryan
/Ryan

ERP Consultant,
Advanced ERP Limited, NZ
https://aerp.co.nz
User avatar
pricerc
Senpai
Senpai
 
Posts: 504
Joined: Mon Aug 10, 2009 12:22 pm
Location: Auckland, NZ
Topics Solved: 20

Re: Identifying and Isolating SQL Server performance issues

Postby Mike.Sheen » Wed Sep 08, 2010 10:51 pm

pricerc wrote:Mike,

the 'actual execution plan' stuff has been there for ages, but I'd never noticed the convenient 'right-click and generate create index script' bit, which I think is a function of SSMS 2008.

I know results may vary, which is why I said the indexes *may* help; they're just a starter for 10 if you're working on a specific query. I've had them make a big difference, and I've had them make negligible difference. Although I've never had it make things worse.

/Ryan


I didn't know that 'right-click and generate create index script' bit existed either! THANKS!

Excellent -thanks, Ryan for your input!
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755


Return to Microsoft SQL Server

Who is online

Users browsing this forum: No registered users and 0 guests

cron