SQL Server Best Practices – Series 5
1.6 Testing SQL
1.6.1 Key SQL
Key SQL should always be tested on high volume data. If you have no idea what high volume is for your product, then ask your analyst. If he does not know tell him to find out.
If you are writing the SQL to return all employees for a company, then test this on data containing 100,000+ employees and 10,000+ companies. If you do not have the test data for it, write a utility to add loads of records.
Additionally, test the performance impact when additional users are added, each simultaneously calling the same query.
Remember, it is important to get the SQL efficient from the outset. With the use of cut & paste common SQL queries reproduce throughout a product like rabbits on a hot day. Tuning a live system involves a lot of additional work.
1.6.2 How Tuned?
When constructing a new SQL statement, run it in Query Analyser or Enterprise Manager with Show Query Plan and Show Stats IO enabled. This will show what SQL Server is doing to return the results. In the plan look for Table Scans on large tables because these are very slow. Try and remove the table scan by adding an index to that column or restructuring the query. The Stats IO option gives a printout of what tables are accessed and how many times they are scanned etc. This can provide valuable information on what table is being accessed the most and can point you in the right direction for adding an index etc.
Regards
Bhagwan Singh Jatav
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
