Investigate potential query performance issues with this general overview for SQL Server Management Studio users. This review will improve the performance of tables and views that have substantial amounts of data and reference indexes.
An index contains keys built from one or more columns in the table or view. These keys are stored within the server and enable SQL Server to find data associated with the keys quickly and efficiently.
When indexes are used, statistics can be created. These are objects within the SQL server that are lightweight. They describe how the data in the tables is distributed. SQL Server has a query optimizer engine that uses statistics to create query plans to improve performance. When statistics are updated, the queries are recompiled so that they are efficient, and the indexes are optimized.
A query plan (or query execution plan) is the sequence of steps used by SQL Server to access the data in the query.
As an example, we will utilize an index from the patient encounter table.
Query:
SET STATISTICS TIME ON
GO
select pe.enc_id, enc_timestamp
from
patient_encounter pe with (nolock)
order by pe.enc_id
GO
SET STATISTICS TIME OFF
GO
Setting the statistics time ON and OFF at the beginning of the query will show exactly how long it takes to execute the query.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
(854246 row(s) affected)
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 781 ms, elapsed time = 4449 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.
Execution Plan:
The execution plan shows how exactly SQL Server gets the results for the query.
How do you check the performance of statistics?
Let’s look at an application database with a SQL Server backend. The query below will bring back results that help identify the performance of statistics for the patient_encounter table in an application database.
Get Stats Query:
Select
OBJECT_NAME(so.object_id) AS [table],
stat.name AS stats_name,
STUFF(
(
SELECT ‘, ‘+cols.name
FROM sys.stats_columns AS statcols
JOIN sys.columns AS cols ON statcols.column_id = cols.column_id
AND statcols.object_id = cols.object_id
WHERE statcols.stats_id = stat.stats_id
AND statcols.object_id = stat.object_id
ORDER BY statcols.stats_column_id FOR XML PATH(”), TYPE
).value(‘.’, ‘NVARCHAR(MAX)’), 1, 2, ”) AS stat_cols,
sp.rows_sampled,
CAST((CAST(sp.rows_sampled AS NUMERIC(20, 8)) / CAST(sp.rows AS NUMERIC(20, 8))) * 100 AS NUMERIC(20, 2)) AS [% Sample] — Index performance Higher is bet
FROM sys.stats AS stat
CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp
JOIN sys.objects AS so ON stat.object_id = so.object_id
JOIN sys.schemas AS sc ON so.schema_id = sc.schema_id
WHERE so.name in (‘patient_encounter’) — Table Name
and stat.name not like ‘_WA_%’ — we want to ignore system generated indices
ORDER BY so.name, 1 DESC
GO
Results
What is this query telling us about the patient_encounter table?
In this example, the patient_encounter table has just under 860,000 rows. The column named % Sample shows the percentage of rows for which the query optimizer is effectively using indexes. In the above example, all indexes are utilizing under 10% of the sample of total rows in that table for all the indexes. In this scenario, the patient_encounter table is causing inferior performance when it is used in q query. To remedy this situation, we want to improve the effectiveness of the statistics. A general rule to follow is that the sample set should be 25% or higher.
How can we update the stats to improve the effectiveness of the queries?
It is important to know that when we update statistics, the query plans are recompiled. When reviewing a query execution plan, if the Actual Number of Rows and the Estimated Number of Rows are significantly different this is an indication of outdated stats. Outdated statistics can lead the optimizer to choose inefficient execution plans that may dramatically impact overall performance. Because of the potential impact on performance, running a stats update should be done outside of production hours. In this case, updating the stats on the patient_encounter table should result in significant performance gains.
The following query will run Update stats for this example. Using the full scan argument does a complete recompile and will set the sample set to 100%
Query:
update statistics patient_encounter with FULLSCAN
GO
Finally, rerunning the Get Stats Query will show that the Sample percentage has been refreshed. This will improve the performance of the indexes.
This general overview will help SQL Server Management Studio users investigate potential query performance issues. For more advanced users, this starting point will help explain the inner workings of how SQL Server performs queries and provide an introduction to how the complex layers of query optimization work.
If you have any questions, please contact us at info@emedapps.com.
