SQL Server Performance Forum – Threads Archive
SQL 2005 slow query executionI have a table with 5 million records (no index) when i select records from it SQL server 2005 takes 2 min 20 sec while SQL SERVER 2000 takes 38 sec on same box. Box has two processors with 2 GB of RAM. any idea why this is happening?
Is the query in a simple SELECT <column_list> FROM <table_name>? Or is it based on a view? Does the query have any filtering? Are the server instances equally busy? They are running on the same hardware, but do they have the same level of resources assigned? If this is a development environment, you could add the following two statements before the query just to make sure data is being read from disk, not from the cache: DBCC DROPFREEBUFFERS
statistics have been updated on the database? Cheers
Hi, Tks for your timely responses, I did find out what was the problem, i was selecting all 6 million records using SQL Server Management Studio for SQL Server 2005 and using Query Analyzer for SQL Server 2000. when i connected to SQL Server 2005 database using Query Analyzer i got the same timings for both SQL Server 2000 and SQL Server 2005. Then connected to SQL Server 2000 database using SQL Server Management Studio, i got the same timings for both SQL Server 2000 and SQL Server 2005. so from what i have noticed is SQL Server Management Studio takes lot of time to display results. I used SET STATISTICS IO ON and SET STATISTICS TIME ON to note readings. Is there any way i can speed up SQL Server Management Studio? or is there a better way to compare performance between SQL SERVER 2000 & SQL SERVER 2005?
Test with SELECT COUNT(*) instead of SELECT <column_list>
Hi, Did try that with query analyzer and SQL Management Studio. Both readings are same, so does SQL Managemnet Studio has overhead as it outputs the result "as it is available" as againts Query Analyzer which outputs the result when query execution is over? Is there any better way to comparison readings between SQL server 2000 & 2005?
Usual recommendation is to have SET NOCOUNT ON at the start of your stored procedures, and probably the same goes for scripts.