Solved

MS SQL server performance problems

Posted on 2012-04-13
10
269 Views
Last Modified: 2012-04-30
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.
0
Comment
Question by:juliandormon
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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
 
LVL 8

Expert Comment

by:tomvergote
Comment Utility
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
 
LVL 13

Expert Comment

by:dwkor
Comment Utility
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
 

Author Comment

by:juliandormon
Comment Utility
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
 
LVL 8

Accepted Solution

by:
tomvergote earned 500 total points
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:juliandormon
Comment Utility
Thanks! I'll try that when it's happening again.
0
 
LVL 7

Expert Comment

by:sqlxl
Comment Utility
Is the whole system running slow or only specific tasks?
0
 

Author Comment

by:juliandormon
Comment Utility
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
 

Author Closing Comment

by:juliandormon
Comment Utility
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
 
LVL 7

Expert Comment

by:sqlxl
Comment Utility
Cool.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now