Friday
Mar232012

SELECT COUNT(*) FROM... without a table scan

Argh!  Table scans hurt.  We found a SROC that was returning a rowcount from a very large table.  Turns out the SPROC was populating a variable with the output of a SELECT COUNT(*) statement ran against a rather large table.

Eliminating the rowcount would've been problematic and time consuming and well... 

So we had to return the rowcount of a very large table to this SPROC somehow without a full table scan.  One option would be to query one of many DMV's included in later versions of SQL Server and that's the route we took.

We did some looking around the web and found a few DMV's up to the task, but permissions and the fact that the rather large table resided within a separate database precluded a "copy/paste" solution.  So we built on the solutions we found in the community (links to follow).

Here's what we did:

  1. Created a function in the database that hosted the table.  The function is called with the parameter of the table name for which you want the rowcount. 
  2. Altered the calling SPROC to populate its rowcount variable with the output of the function.
  3. Adjusted the calling users' permissions so that they could EXECUTE the function and query the DMV's leveraged within it.
    As with everything else the SQL Server product offers a plethora of ways to accommodate the elevated permissions required to access the DMV's leveraged by your function:
  • Granting VIEW SERVER STATE
  • - this allows the user to query DMV's for multiple databases (server scope)
  • Granting VIEW DATABASE STATE - allows the user to query DMV's for a specific database (database scope)
  • Embedding WITH EXECUTE AS OWNER - executing the function as the dbo role (object scope - though do be careful when delegating rights to the function)

We opted for the last option:  creating a function that leveraged WITH EXECUTE AS OWNER.

Without further ado here's the resulting function.  Create this in the database hosting the tables for which you wish to get rowcounts more efficiently:
Here's a code snippet to perform a quick sanity check on the function:

Ideally we would've liked to have made a single function in a database widely accessible to all.  That would've required passing the database name as a parameter.  Sadly functions do not allow for dynamic SQL.  While a SPROC could do this easily it requires dynamic SQL which would have issues passing its results to populate a local variable for consumption by the caller.  Hard-coding the database name within the SPROC would've been problematic for us.  If you have a more elegant solution in mind please do share.

Here's a list of links on which we based our analysis with a short description of what they contain.  I'm not affiliated with these people in any way - other than to extend my thanks for making their work available:
http://www.ideaexcursion.com/2008/10/01/displaying-row-count-for-all-tables/ - script for getting rowcounts without full table scans
http://msdn.microsoft.com/en-us/library/ms188754.aspx - contains information on DMV's and the permissions required to access them
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=148916 - interesting thread regarding tracking table growth
http://msdn.microsoft.com/en-us/library/ms188001(v=sql.90).aspx – dynamic sql with sp_executesql
http://msdn.microsoft.com/en-us/library/ms175170.aspx  - another great link about dynamic SQL
http://msdn.microsoft.com/en-us/library/ms378108.aspx  - returning parameters using SPROC's
http://sqlserver2000.databases.aspfaq.com/how-do-i-get-the-result-of-dynamic-sql-into-a-variable.html  - more on leveraging dynamic SQL

Sunday
Aug292010

Get rid of Key Lookups

If you see  "Key Lookup" in your execution plan you can build a covering index to speed things up.  Doing a Key Lookup is less optimal than an Index Seek, so when you see these in your execution plans know that you have the option of making this particular operation faster.  Albeit with the chance of adding overhead to the maintenance of your existing index.

Generate your execution plan and hone in on the Key Lookup operations like the one below:

Look at your Nested Loops operation and identifity the columns it's looking for.  An easy way to do this is to right-click on the operation when viewing your execution plan then navigating to Properties - or mashing F4 when it's selected.

Now go to Object Explorer and script out the existing index.  Under Included columns add all columns that are contained in the Nested Loop operation that aren't already in the existing index.

An included column is adding a non-key column to the leaf level of the index.  There will be a slight overhead to your index upkeep, but if this is an often-executed operation it might be well worth it!