views:

147

answers:

4

Hello everyone,

I am developing using VSTS 2008 + C# + .Net 3.5 + ASP.Net + SQL Server 2008 Express. I am working on 32-bit platform with Windows Server 2003. The issue I find is simple SQL statement like select/create a simple table is very small from SQL Server Management Studio.

BTW: I think it should be machine specific issue since the same code runs correctly on another machine. But I am not 100% sure. May be this machine triggers some bug in my code?

Any ideas how to analyze to find the low performance bottleneck?

thanks in advance, George

+3  A: 

A good start is to use SQL Server Profiler to identify the long-running queries.

Once you have an idea about which ones are problematic, you can use SQL Server Management Studio to examine their query plans to see if you should add any indexes, etc.

Mark Seemann
Can we use SQL Server Profiler for SQL Server (free) Express edition? I doubt. :-)
George2
And how to detect whether it is SQL Server (or my code issue) or machine environment issue which impacts SQL performance?
George2
SQL Server Profiler is a tool that ships with SQL Server. It is not available for Express, but if you have a full SQL Server on another machine, you can profile a remote Express database.
Mark Seemann
Detecting whether you have an issue with SQL Server or something else is done by exclusion. Start by profiling SQL Server with the Profiler because it's so easy. If you can't find an issue with the database, it must be somewhere else, but those issues can be a lot harder to track down, which is why I recommend starting by profiling the database.
Mark Seemann
Cool! Thanks Mark! Two issues dealing with using SQL Server Profiler. 1. if I am not in a active directory (or windows domain) environment, how to connect from remote machine? 2. how to identify if there are issues from SQL Server Profiler output?
George2
If you're not on the domain, you either need to runas an appropriate user on the other domain, or use SQL Server username/password authentication if that is enabled.
Mark Seemann
The easiest way to identify issues is to look at the query time. Look for long-running queries.
Mark Seemann
+1  A: 

In addition to the previous answer you could , if your account has permission, monitor locks on database objects while your query is running poorly. Also, you can run the index tuning wizard to see what it says.

Jride
And how to detect whether it is SQL Server (or my code issue) or machine environment issue which impacts SQL performance?
George2
if there are no locks, the SQL profile looks good and there appears to be all the right indexes you have to start looking at code/environment. Does the query run well from enterprise manager on the same machine?
Jride
"monitor locks on database objects while your query is running poorly" and "you can run the index tuning wizard " -- can you show me how?
George2
"SQL profile" -- can not use this tool from SQL Server express edition.
George2
+1  A: 

If you think it is a machine specific issue, a good start is to monitor the usage of RAM by Sql Server, executing this sentence:

DBCC MEMORYSTATUS;
despart
Then how to analyze based on this command output?
George2
Can we use this command from SQL Server (free) Express edition?
George2
+1  A: 

If it works fine on some machines but not others, make sure the machine it is running slower on has the same indexing as the faster one (it is surprising how often the indexes don't get moved to the other machine) and update the statistics.

It can also be much slower if the hardware is less robust on the slower machine.

Profiler and execution plans can also help identify bottlenecks and performance problems.

Check for deadlocks as well, often code that works fine by itself can conflict with something else the production server is running at the same time.

Also many developers create code on a machine that has much less load and far less data than the eventual production machine and then are surprised when the code is slow on prod. If you didn't have a full data set on the dev machine, you are running serious risks of writing poorly performing code. If you don't do load testing, maybe you should.

And why develop using Express if the final will not be in Express, buy Developer version and have all the tools you need to tune and monitor while developing.

Are other applications running on the machine that is slow? It is generally a poor practice to run anything except SQL Server on a machine as SQl Server will want all the memory of the machine.

HLGEM
"Profiler and execution plans can also help identify bottlenecks and performance problems." -- I want to learn more about this topic, any referenced documents?
George2