Major performance hit in select statement once database reaches 50,000 records

I'm running SQL Server 2005 (64bit) Evaluation Edition. I'm doing an evaluation on performance for an internal project here at work.

The machine has 2GB of RAM and is an AMD 64 running Windows XP 64 with SQL Server 2005 64 Eval

The database has about 200,000 records in the person table.

Select * from person is the statements "very simple"

The results up to 49,000 are acceptable then at 50,000 it goes into a crawl and starts returniong 100 records every 2 seconds.

Does anyone know what is going on here?

I assume you have a very good reason for sending tens of thousands of records back to a client... You may easily solve your problem by using something like a where clause.

The number of records in a table likely has little to do with the issue, more likely is that is has to do with the overall number of pages. For something like a SELECT * the very best plan you can get is a clustered index scan, providing you have a clustered index. Barring the presence of a clustered index you'll see a table scan.

Either one of these plans are going to generate a substantial amount of disk IO. On a machine with 2GB of ram the maximum size for data cache will be around 1.6GB, my guess is that your table exceeds that size and this simple query is creating a great deal of activity on your disk drives.

Another issue is going to be concurrency. For a SELECT * you might well be attempting to acquire a table lock up front.

Start by getting the query plan for your statement. You can do that with either SET STATISTICS PROFILE ON or SET STATISTICS XML ON.

You should also have a look at the amount of disk IO generated by the query, and the performance of your disk drives.

SET STATISTICS IO ON and SET STATISTICS TIME ON will help out there, and you'll need to start perfmon and have a look at the physical disk counters for avg disk sec/read, avg disk sec/write, avg disk sec/transfer for the drives where your data and log files are located. Anything over 10ms is cause for concern.

Be sure to try from different clients as well to rule out any client issues.

|||

Thank you for the input. I ran it again with your recommendations and yes my Disk Write is max'd out completley. The table has 174,000 records of sample data and the query took 1Hour and 29Minutes.

Here is what I got back from prefixing my SQL Select Statement:

SET STATISTICS PROFILE ON

SET STATISTICS IO ON

SET STATISTICS TIME ON

select * from consultants;

174511 1 select * from consultants; 1 1 0 NULL NULL NULL NULL 174511 NULL NULL NULL 20.51673 NULL NULL SELECT 0 NULL

174511 1 |--Clustered Index Scan(OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK])) 1 2 1 Clustered Index Scan Clustered Index Scan OBJECT:([NewRR].[dbo].[Consultants].[aaaaaConsultants_PK]) [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. 174511 20.32461 0.1921191 7222 20.51673 [NewRR].[dbo].[Consultants].[ConsIntID], [NewRR].[dbo].[Consultants].[ConsultantID], [NewRR].[dbo].[Consultants].[Title], [NewRR].[dbo].[Consultants].[FirstName], [NewRR].[dbo].[Consultants].[MiddleName], [NewRR].[dbo].[Consultants].[LastName], [NewRR].[dbo].[Consultants].[Suffix], [NewRR].[dbo].[Consultants].[NickName], [NewRR].[dbo].[Consultants].[DisplayName], [NewRR].[dbo].[Consultants].[CompanyName], [NewRR].[dbo].[Consultants].[Available], [NewRR].[dbo].[Consultants].[AvailabilityDate], [NewRR].[dbo].[Consultants].[AvailabilityNotice], [NewRR].[dbo].[Consultants].[JobTitle], [NewRR].[dbo].[Consultants].[PrimarySkills], [NewRR].[dbo].[Consultants].[SecondarySkills], [NewRR].[dbo].[Consultants].[OtherSkills], [NewRR].[dbo].[Consultants].[TotalExp], [NewRR].[dbo].[Consultants].[USExp], [NewRR].[dbo].[Consultants].[CommSkills], [NewRR].[dbo].[Consultants].[Rate], [NewRR].[dbo].[Consultants].[Relocation], [NewRR].[dbo].[Consultants].[ResumeDir], [NewRR].[dbo].[Consultants].[ResumeFile], [NewRR].[dbo].[Consultants].[ModifiedResumeDir], [NewRR].[dbo].[Consultants].[ModifiedResumeFile], [NewRR].[dbo].[Consultants].[ResumeWebPath], [NewRR].[dbo].[Consultants].[ReferredBy], [NewRR].[dbo].[Consultants].[Summary], [NewRR].[dbo].[Consultants].[AdditionalInfo], [NewRR].[dbo].[Consultants].[XMLResume], [NewRR].[dbo].[Consultants].[SSN], [NewRR].[dbo].[Consultants].[VisaStatus], [NewRR].[dbo].[Consultants].[VisaExpiryDate], [NewRR].[dbo].[Consultants].[Address1], [NewRR].[dbo].[Consultants].[Address2], [NewRR].[dbo].[Consultants].[Address3], [NewRR].[dbo].[Consultants].[City], [NewRR].[dbo].[Consultants].[State], [NewRR].[dbo].[Consultants].[ZipCode], [NewRR].[dbo].[Consultants].[Country], [NewRR].[dbo].[Consultants].[HomePhone], [NewRR].[dbo].[Consultants].[WorkPhone], [NewRR].[dbo].[Consultants].[MobilePhone], [NewRR].[dbo].[Consultants].[Fax], [NewRR].[dbo].[Consultants].[EMail1], [NewRR].[dbo].[Consultants].[EMail2], [NewRR].[dbo].[Consultants].[Salary], [NewRR].[dbo].[Consultants].[SalaryReviewDate], [NewRR].[dbo].[Consultants].[BonusAmount], [NewRR].[dbo].[Consultants].[BonusAmountDate], [NewRR].[dbo].[Consultants].[DOE], [NewRR].[dbo].[Consultants].[DOT], [NewRR].[dbo].[Consultants].[DOB], [NewRR].[dbo].[Consultants].[DOM], [NewRR].[dbo].[Consultants].[SpouseName], [NewRR].[dbo].[Consultants].[EmergencyContactName], [NewRR].[dbo].[Consultants].[EmergencyPhone], [NewRR].[dbo].[Consultants].[Notes], [NewRR].[dbo].[Consultants].[Archived], [NewRR].[dbo].[Consultants].[SendInHotList], [NewRR].[dbo].[Consultants].[Employee], [NewRR].[dbo].[Consultants].[JobType], [NewRR].[dbo].[Consultants].[Categories], [NewRR].[dbo].[Consultants].[Groups], [NewRR].[dbo].[Consultants].[Owners], [NewRR].[dbo].[Consultants].[EmployeeNumber], [NewRR].[dbo].[Consultants].[OnHold], [NewRR].[dbo].[Consultants].[OnHoldTill], [NewRR].[dbo].[Consultants].[VacationDays], [NewRR].[dbo].[Consultants].[SickDays], [NewRR].[dbo].[Consultants].[TableHolidays], [NewRR].[dbo].[Consultants].[FloatHolidays], [NewRR].[dbo].[Consultants].[LinkToIntID], [NewRR].[dbo].[Consultants].[UserIDs], [NewRR].[dbo].[Consultants].[Private], [NewRR].[dbo].[Consultants].[CreateDate], [NewRR].[dbo].[Consultants].[EditDate], [NewRR].[dbo].[Consultants].[MergeDate], [NewRR].[dbo].[Consultants].[UserField1], [NewRR].[dbo].[Consultants].[UserField2], [NewRR].[dbo].[Consultants].[UserField3], [NewRR].[dbo].[Consultants].[UserField4], [NewRR].[dbo].[Consultants].[UserField5], [NewRR].[dbo].[Consultants].[UserField6], [NewRR].[dbo].[Consultants].[UserField7], [NewRR].[dbo].[Consultants].[UserField8], [NewRR].[dbo].[Consultants].[UserField9], [NewRR].[dbo].[Consultants].[UserField10], [NewRR].[dbo].[Consultants].[Field1], [NewRR].[dbo].[Consultants].[Field2], [NewRR].[dbo].[Consultants].[Field3], [NewRR].[dbo].[Consultants].[uuManager], [NewRR].[dbo].[Consultants].[uuResumeText], [NewRR].[dbo].[Consultants].[uuResponsibilites], [NewRR].[dbo].[Consultants].[uuStartDate], [NewRR].[dbo].[Consul.. NULL PLAN_ROW 0 1

Any guidance would be appreciated.

|||

Well - that certainly is a wide table. Again I assume you have a good reason to send back tens of thousands of rows to a client. If this is for a performance test, I hope this is not indicative of how the application is written.

Since you already have a clustered index scan in the plan the only thing that might help you out here is to defrag the index, assuming you have not already done so.

I find it odd that your disk writes are impacted. There should be no write activity at all generated by SQL Server during execution of this statement, if you have writes then you need to find out what is using your drive and stop it.

I'm really curious about the client though, it seems that results should start flowing immediately. Can you replicate this same behavior from SQLCMD and managment studio?

|||Perhaps it is spooling to tempdb?|||How do u know the disk size is max out