SQL Server includes a DMV, sys.dm_exec_query_stats, that returns performance statistics for each query plan cached in memory. However, it can also help give you insight into how consistent your developers are with writing code.For this topic, we’ll just concentrate on a few columns returned by the DMV: sql_handle and plan_handle. Per Books Online, sql_handle is a value that refers to the batch or stored procedure that the query, and plan_handle is a value that refers to the compiled plan of that query. For each query that is processed, SQL Server can generate one or more compiled plans for that query. This one-to-many relationship can be caused by a number...
Continue reading...Internals
T-SQL Tuesday #40 – Proportional Fill within a Filegroup
T-SQL Tuesday #40 is underway, and this month’s host is Jennifer McCown (blog|twitter). The topic is about File and Filegroup Wisdom. Jennifer says she’s a big fan of the basics, so I thought I would talk about the basics of proportional fill within a filegroup. This should be pretty common knowledge, but I still talk to a lot of DBAs that don’t know anything about it, or if they have heard of it, they still don’t know how it works. The proportional fill algorithm is used to keep the amount of free space within a filegroup evenly distributed across all...
Continue reading...The Performance Impact to Prefix Stored Procedures with sp_
Last week I ran across a blog post by Axel Achten (B|T) that outlined a few reasons why you should not use SELECT * in queries. In the post, Axel used the SQLQueryStress tool by Adam Machanic (B|T) to stress-test a simple query using SELECT * and SELECT col1, col2,… This gave me an idea to use the same SQLQueryStress tool to benchmark a stored procedure that’s prefixed with sp_. All DBAs know, or should know, you should not prefix stored procedures with sp_. Even Microsoft mentions the sp_ prefix is reserved for system stored procedures in Books Online. I’m not going to discuss the do’s and don’ts of naming conventions. What I want...
Continue reading...What is DBCC SHOW_STATISTICS Telling Me About My Data?
Per Books Online, DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. Basically it shows you the statistics, or a summary of the data, that SQL Server will use to help generate an execution plan. In the example below, we’ll be looking at the statistics for the index IX_Person_LastName_FirstName_MiddleName, which is a non-clustered index on the Person.Person table in the AdventureWorks2012 database. We’ll cover several queries you can run against your data to help you visualize what the statistics are telling you about your data. Before we dive into the example, we need to update the statistics with a full scan...
Continue reading...
