December 20, 2011 at 9:07 am
I ran two queries that give the number of rows in a table, and I wanted to see which one had better performance. I ran them both at the same time and hit Include Actual Execution Plan, not really expecting anything interesting to happen. But I was surprised. The two queries are:
SELECT COUNT(*) AS [Total Rows]
FROM Person.Contact;
EXEC sp_spaceused 'Person.Contact';
Both gave row count as 19,972 in two grids. Under the Messages tab though, was this:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Where was this 3rd row affected. Then in the execution plan, rather than listing the two queries, there are 5 query plans. Query 1 is the SELECT statement from above. Queries 2-5 are the background (I assume) the sp_spaceused. I was not expecting this at all. If sp_spaceused is really 4 different queries in one, then it seems valid to assume other built-in procedures perform similarly.
So question is, Should we be using the built-in procedures if there is a basic Select..From statement that produces the same result? And if anyone knows of a good white paper on the subject, that would be very helpful.
Thank you, Amy
December 20, 2011 at 9:25 am
Some of the builtin procs do use multiple queries and also utilize cursors to generate the result.
In the case of spaceused, I prefer to use this query.
http://jasonbrimhall.info/2011/11/17/table-space-revised-again/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply