SQL 2005

Question

I have a client with a Server 2003 64-bit server with 20GB of RAM. Application(let's call it XYZ) uses SQL 2005 as a backend. 12GB of RAM has been allocated for the SQL and 8GB for the OS. The OS is installed on the C drive and all SQL data is on a RAID-1 SAS drives on a different drive(O drive). Users experience slow fucntionnality within software XYZ. I have tried disabling the AV and ensure no backups run during the day. I used perfmon to monitor activity and it's 99% idle during the day. I have worked with software XYZ and claimed it's not their software. I will also try the following:

1) replace network cable from server to switch;

2) Plug network cable to another port on a new switch;

3) Work with HP to identity if there's any hardware issues;

4) Call Microsoft for assistance.

Your input would be greatly appreciated!

Answer 1

Hello Jose,

Slow application response can have several reasons, for example unmaintained indexes / statistics or missiong indexes.

Missing indexes with CREATE statement for it

---

Olaf Helper

Blog Xing

Answer 2

So I should run the following commands on the SQL server? I'm not the application DBA and don't want to cause any further issue. But I will call Microsoft's SQL team and get help. Thanks Olaf!

Answer 3

Jose,

I assume you get the same answer from the Microsoft guys.
I agree to Olaf - have a look to the execution plans / run traces.

If you are not responsible for the sql server ask the responsible dba - it is his job to detect and avoid bottlenecks if they are coming from sql server!

I would assume bad indexes, wrong indexes (based on the amount of data), fragmentation of indexes, missing statistics, old statistics, horrible query plans.

---

Uwe Ricken

MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 4

Uwe,

       Do you know of any query to detect any wrong/bad indexes/fragmentation of indexes/missing statistics/old statistics/horrible query plans?

Thanks!

Answer 5

Uwe,

       Do you know of any query to detect any wrong/bad indexes/fragmentation of indexes/missing statistics/old statistics/horrible query plans?

Thanks!

Hallo Jose,

yes - for sure :)

To get details about index fragmentation you have to use sys.dm_db_index_physical_stats.

For information about the usage of indexes you have to use sys.dm_db_index_usage_stats

Find detailed information about both system functions here:

http://msdn.microsoft.com/en-us/library/ms188917.aspx

http://msdn.microsoft.com/en-us/library/ms188755.aspx

---

Uwe Ricken

MCSE - SQL Server 2012
MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)