Performance questions

We have a production database that sits on a 4 proc server with 4 GB of memory and SAN disk storage via fiber. There are some stored procedures that run and they take approximately 10 minutes to run. A developer has SQL Server installed on his local pc that has 1 2.5 GHz processor and 2 GB of memory and the stored procedures run in approximately 2 minutes. I have updated statistics and rebuilt indexes to no avail. He is questioning why it runs so much faster on his smaller pc compared to the production environment. I have monitored CPU, Memory, and Disk Queue Length and none of these performance counters look concerning to me while the stored procedures are running.

Can anyone out there give me some input on what I could check to figure out why we are experiencing this performance difference?

Thanks,

Corey

Do both databases have the same volume of data? You did not mention what speed and type of processors were in the server. If it is an older server, with say, 1GHz Pentium 3 based Xeon CPU's, you could easily see much slower query performance than on a developer's workstation, with a single faster CPU.

You should also look at the graphical execution plan for the query on the server, and see whether or not the plan is being parallelized or not. BTW, what version of SQL Server are you running?

|||

The data is the same between the two environments. His pc has a 2.5 GHz Intel processor and our server has 4 2.7 GHz Intel processors. We are running SQL Server 2000 SP3a. I will try to look at the queries but their are hundreds. Should the query plan be the same since I ran update statistics, rebuild indexes, and then the process so that I was comparing as each as close as possible.

|||

Just pick two or three commonly executed queries and run them in Query Analyzer with the graphical execution plan turned on and with SET STATISTICS IO on (just run that statement before you run the queries).

Do you see similar query plans and I/O statistics between the developer's workstation and your server?

Are we comparing a developer's workstation with no load with a production server with a full-work load?

If so, I would start looking at the production server to see if you see signs of CPU pressure, memory pressure, I/O bottlenecks, etc.

|||

I will try to get a couple of queries and compare them as suggested.

The developer's workstation has no load and the production server does have other work going on but this server does have plenty of resources left and it is a much larger box than the developer's workstation. I will go ahead and grab some perfmon counters to verify this too.

sql

Performance of NVARCHAR(n) for large strings

What are the performance or storage implications of using a large value for NVARCHAR? For example, if I specify a NVARCHAR(4000) column just to cope with the rare case there are strings that long, but have a table full of strings 255 characters long, is the performance identical to specifying an NVARCHAR(255) column? Is there a reason then NOT to specify NVARCHAR(4000) on everything? i.e. does the query optimizer use it?

I know how long a row is and how many rows can fit in a page (4096 bytes) affects performance, but my understanding is that NVARCHAR only stores the characters needed so it wouldn't be affected unless there was actually a longer string. I wanted to know if there are any other considerations to using a large value here.

Also, how does NTEXT compare to using NVARCHAR? I noticed the documentation said it used a new page after 256 characters, which sounds like it's different from how a 4000 byte NVARCHAR would be stored?

Specifying nvarchar(4000) as opposed to nvarchar(256) when you only intend to store strings of max length 256 should not cause any significant perf hit.

On the other hand, nvarchar should be the preferable choice when compared to ntext for performance reasons. First of all, ntext (or image) cannot be indexed. Secondly, they are stored out of the row page and incur more overhead.