asked on

MS SQL server performance problems

Running the following set up for our dedicated MS SQL server.

Windows Server 2003 SP2 32 Bit
12 GBs of Ram
2.25 GHz Intel Zeon L5520
8 Cores

Microsoft SQL Server Management Studio 9.00.4035.00
Microsoft Analysis Services Client Tools 2005.090.4035.00
Microsoft Data Access Components (MDAC) 2000.086.3959.00 (srv03_sp2_rtm.070216-1710)
Microsoft MSXML 2.6 3.0 4.0 6.0
Microsoft Internet Explorer 8.0.6001.18702
Microsoft .NET Framework 2.0.50727.3625
Operating System 5.2.3790

When things are running normally we are consuming between 35 - 40% CPU and about a 30% of our available memory. 9.91GBs PF Usage.

The DB is quite large, about 3.5GBs judging by the backup file size.
The transaction logs are stored on a separate disc.
We have one particularly large table with 1,252,803 records.

So for a several years we have had no problems running this DB and this box but lately things have been going awry. For starters, the scheduled maintenance kept failing when trying to reorganize indexes. I have since excluded that and now must rebuild indexes and stats nightly.
About once or twice a week, we notice that the CPU usage spikes to 100% and stays there and DB reads slow to a crawl. Nothing will bring the CPU usage back to normal levels (including restarting the SQL service) except rebuilding the indexes and sometimes the stats as well.

I have very little experience debugging such problems (basically I know nothing) so I'm hoping one of you fine experts can help me debug and solve this problem.

Thanks in advance.

Anthony Perkins

First, let's put things in perspective:
The DB is quite large, about 3.5GBs judging by the backup file size.
3.5GB is a very small database.

We have one particularly large table with 1,252,803 records.
This is not a large table by any means.

I have very little experience debugging such problems (basically I know nothing) so I'm hoping one of you fine experts can help me debug and solve this problem.
If you are serious about this, it is time you hired a reputable SQL Server consultant to help you identify and correct the problems you are having.

tomvergote

it's not gonna be a flip of a switch solution.
First you 'll have to find the bottleneck using wait stats and performance counters, you have to review your queries and indexing.
I'm afraid hiring someone or reading up will be required

dwkor

Do you use extended memory (e.g. have it correctly configured)? What does target server memory performance counter say? Based on what you said about memory utilization and your database size I would not be surprised if it's not on. If this is the case, that should be the first (and very simple) fix.

Overall, 12Gb of RAM for 32 bit OS is a bit more than I'd suggest to have - perhaps you want to switch to 64 bit at some point.

As other said, it's impossible to diagnose the system based on that information. I'd also suggest you to bring somebody on board. Good DBA professional can do the system troubleshooting very fast..

juliandormon

ASKER

OK will do. But in the meantime the problem just happened again and I ran the following query to see what was perhaps causing the spike. Does any of this help point in the right direction?

C:\Documents and Settings\Administrator>osql -E
1> select top 2 * from sysprocesses order by CPU desc
2> go
spid kpid blocked waittype waittime
lastwaittype
waitresource

dbid uid cpu
physical_io memusage login_time
last_batch ecid open_tran status

sid

hostname

program_name

hostprocess
cmd
nt_domain

nt_username

net_address
net_library
loginame

context_info

sql_handle stmt_start stmt_end
request_id
------ ------ ------- -------- --------------------
--------------------------------
------------------------------------------------------------------------
(truncated for brevity by me)

------------------------------------------
------------------------------------------ ----------- -----------
-----------
10 1884 0 0x0081 457296
CHECKPOINT_QUEUE

1 1 54921
30887 0 2012-03-27 06:33:47.167
2012-03-27 06:33:47.167 0 0 background

0x0100000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000

CHECKPOINT

sa

0x0000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000
0x0000000000000000000000000000000000000000 0 0
0
398 0 0 0x0000 0
MISCELLANEOUS

7 1 37593
0 2 2012-04-15 12:44:16.610
2012-04-15 12:54:08.680 0 0 sleeping

0xD9BAD325A951474ABA5ACB9748C0C6BC00000000000000000000000000000000000000

000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000
WEBSERVER2B

Internet Information Services

17688
AWAITING COMMAND

A4BADB20E37D
TCP/IP
lac_SQL123

0x0000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000000000000000000000000000000000

000000000000000000000000000000000000000000
0x030007002CA1AF275F30DB00D69F000001000000 0 0
0

(2 rows affected)
1>

ASKER CERTIFIED SOLUTION

tomvergote

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

juliandormon

ASKER

Thanks! I'll try that when it's happening again.

sqlxl

Is the whole system running slow or only specific tasks?

juliandormon

ASKER

Hired a DBA as suggested. He found that there were 3 indexes being used for the same table. NOt sure I quite understand why or how this happened. Either way, deleting the duplicate (or extra) indexes corrected our problem.

juliandormon

ASKER

sqlxl

Cool.