I have read that SQL Server Compact Edition can be managed within SQL Server Management Studio Express.
Can anyone show me how to do it?
Or I can manage the Compact Edition in other way instead of in VS 2005.
Thanks a lot,
JD
SQL Server Management Studio Express SP2 will allow you to manage SQL Compact Edition (despite the statement on the download page). It can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=6053C6F8-82C8-479C-B25B-9ACA13141C9E&displaylang=en
The reason I ask is that with our product we're trying to maintain compatibility with both SE and EE. I realize I can just install SE instead of DE, but if there's a way to simple toggle the behavior, that would be even better (especially since I already have DE installed. :)
A partial answer:
If you have a BI project (that you open with Visual Studio to design and then deploy to AS2005), there is a project setting for specifying the Edition (right click on the project node, use 'Properties' and notice the edition setting at the top of the page). This will affect the validations to be done in the tools only (in Visual Studio for the BI project), the AS2005 server will do his own validations (based on its Developer Edition that you mention).
Adrian Dumitrascu.
|||Ah, good tip, thanks. Unfortunately, that's not quite good enough for us, because we're doing programmatic manipulations of the cube definition using AMO. But it's good to know about, nonetheless.In this case, you can use the AMO's Validations. After you create or change a major object with AMO, before you call the .Update method, use the .Validate method, passing it explicitly the StandardEdition (otherwise it will use the engine's edition).
Sample code:
Cube myCube = ...;
ValidationErrorCollection errors = new ValidationErrorCollection();
myCube.Validate(errors, true, ServerEdition.Standard); // 'true' = include detailed errors, no problem to use 'false' for our scenario
if (errors.Count > 0)
{
// We either specified something invalid or we used features unsupported in the standard edition
foreach (ValidationError error in errors)
{
Console.WriteLine(error.FullErrorText);
}
}
Adrian.
|||Thanks Adrian. I would prefer to be able to just fix the server level, so I don't need to change the code to support this (once the product is deployed, these checks aren't really necessary). But in the absense of that capability, I can probably make do with the validation stuff.I asked around and we don't have a setting per server to downgrade the edition. You could install the standard edition as a named instance eventually (side-by-side with your current developer edition).
Adrian Dumitrascu.
|||Well, it's nice to know definitively. Thanks for the info. Feel free to add that to the enhancement request list for the next version. ;)HTH, jens Suessmeyer.
http://www.sqlserver2005.de
HTH, jens Suessmeyer.
http://www.sqlserver2005.de
I am writing code with a team using continuous integration where the connection string is the following:
"Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=Secret;Trusted_Connection=Yes;"
is there a way (create an alias,rename server, other?) to make my local dev SQL server accept the connection string above, as at the moment I have to rename the connection string every time before checking in code and after downloading the latest version from the code repository.
any advice or tips will be greatly appreciated
Thanks
JW.Hi,
setup an alias using the SQL Server Configuration manager. Configure a new alias to listen to .\sqlexpress and redirect to the installed dev server.
HTH; Jens K. Suessmeyer.
http://www.sqlserver2005.de
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
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
