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.
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 :
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 :
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).