CODEWITHSUNDEEP
c#asp.net.netsql-server-2008visual-studio-2008
George2
George2

Reputation: 45781

analyze SQL Server performance issue?

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?

Upvotes: 3

Views: 573

Answers (4)

HLGEM
HLGEM

Reputation: 96590

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.

Upvotes: 1

David Espart
David Espart

Reputation: 11780

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;

Upvotes: 1

Jeremy Samuel
Jeremy Samuel

Reputation: 790

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.

Upvotes: 1

Mark Seemann
Mark Seemann

Reputation: 233172

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.

Upvotes: 3

Related Questions