Latest News
Monday, June 10, 2013 12:03 PM
Going to SQL Relay?
Going to SQL Relay? Show some support and impress your friends and colleagues with these two badges. Not going to SQL Relay? Why not! Go to the SQL Relay web site and find out where your nearest event is. “I’m speaking at SQL Relay” <a href="http://www.SQLRelay.co.uk"><img src="http://www.SQLRelay.co.uk/images/SQLRelaySpeaking.png"></a> “I’m going to SQL Relay” <a href="http://www.SQLRelay.co.uk"><img src="http://www.SQLRelay.co.uk/images/SQLRelayGoing.png"></a>
Wednesday, May 15, 2013 10:46 AM
How many rows in that table?
How many of us have typed ‘select count(*) from TABLE’ when we want to know how many rows in a table? There is another way, by querying sys.partitions. I’ve compared the performance and IO. .csharpcode, .csharpcode pre{ font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/}.csharpcode pre { margin: 0em; }.csharpcode .rem { color: #008000; }.csharpcode .kwrd { color: #0000ff; }.csharpcode .str { color: #006080; }.csharpcode .op { color: #0000c0; }.csharpcode .preproc { color: #cc6633; }.csharpcode .asp { background-color: #ffff00; }.csharpcode .html { color: #800000; }.csharpcode .attr { color: #ff0000; }.csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em;}.csharpcode .lnum { color: #606060; } 1: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 2: 3: SET STATISTICS TIME ON; 4: 5: SET STATISTICS IO ON; 6: 7: SELECT COUNT(1) 8: FROM MYBIGTABLE 9: 10: DECLARE @objid AS BIGINT; 11: 12: SELECT @objid = object_id 13: FROM sys.tables 14: WHERE name = 'MYBIGTABLE'; 15: 16: SELECT SUM(rows) 17: FROM sys.partitions 18: WHERE index_id IN (0, 1) 19: AND [object_id] = @objid; 20: 21: SET STATISTICS TIME OFF; 22: 23: SET STATISTICS IO OFF; The results from STATISTICS IO and TIME are as follows;Table 'MYBIGTABLE'. Scan count 1, logical reads 115328, physical reads 0, read-ahead reads 115328, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 27628 ms, elapsed time = 61186 ms.So over a minute.For the query on sys.partitions;Table 'syssingleobjrefs'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'sysidxstats'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.Table 'sysschobjs'. Scan count 1, logical reads 4, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 16 ms, elapsed time = 140 ms.(1 row(s) affected)Table 'sysrowsets'. Scan count 1, logical reads 10, physical reads 0, read-ahead reads 7, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.SQL Server Execution Times: CPU time = 0 ms, elapsed time = 35 ms.So 140+35=175ms, so less than a quarter of a second compared with 61 seconds.Much much much much much much faster!
Monday, April 08, 2013 9:10 AM
Technology: Why I Hate and Love you so…
Monday, April 08, 2013 9:02 AM
More Blogging…
Sunday, December 16, 2012 3:31 PM
12 days of Geekmas..on the 3rd day
Tuesday, November 27, 2012 12:00 AM
#BADSQL 2
Monday, November 26, 2012 2:53 AM
SQL Event Calendar
Usage Stats: (Tidies)
Today:
8
Last 7 days:
3854
Last 30 Days:
16574
This Year:
291708