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.
juliandormonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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.
0
tomvergoteCommented:
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
0
dwkorCommented:
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..
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

juliandormonAuthor Commented:
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>
0
tomvergoteCommented:
you should try to get the query that's behind it and optimize it.
SELECT session_id,
       request_id,
       Db_name(database_id),
       start_time,
       status,
       command,
       Substring(txt.TEXT, ( statement_start_offset / 2 ) + 1,
       ( ( CASE statement_end_offset
       WHEN -1 THEN Datalength(txt.TEXT)
       ELSE statement_end_offset
                                                                   END
           - statement_start_offset ) / 2 ) + 1) AS statement_text,
       wait_type,
       wait_time,
       blocking_session_id,
       percent_complete,
       cpu_time,
       reads,
       writes,
       logical_reads,
       row_count
FROM   sys.dm_exec_requests
       CROSS APPLY sys.Dm_exec_sql_text([sql_handle]) AS txt
WHERE  session_id <> @@SPID
       AND session_id > 50

should get you the currently executing queries with the actual query, get those and try to optimize them using the database engine tuning advisor.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
juliandormonAuthor Commented:
Thanks! I'll try that when it's happening again.
0
sqlxlCommented:
Is the whole system running slow or only specific tasks?
0
juliandormonAuthor Commented:
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.
0
juliandormonAuthor Commented:
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.
0
sqlxlCommented:
Cool.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.