Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: SQL-server struggles after a database-restore

    Hello

    I am struggling to get the speed of my databaseserver/database to run as fast as it did.


    Question: What are my options that I should check in SQL (management studio), after a complete restore of the server and database if it does run slowly?

    Some background:
    A complete restore of the server and database is done.
    Every now and then (quite often actually) a program that I have not created runs slow, so it seems to me, that if the program "hits" certain tables/indexes the speed decreases (a query then takes 7-10 seconds, which normally should have taken 0.5). I cannot see the queries ran, so those I cannot test for. Theres about 300-350 tables in the database, and they're "not mine", hence I am at a loss of which tables are in use, etc.

    How can I know its slow, and am I sure it is the database and not hardware/server?
    Users are complanining and I've noticed it myself, even the simplest queries runs slow. The server runs at normal speed, opening other tools and programs on the server gives a quick response, its the queries that sometimes uses 10 times longer time to execute. But of course, while a "large query" is running against the database, doing other things takes the server to its knees, so there must be some large queries going on, but I want to find out if it is the CPU, RAM, Disc, ... I want to find the bottleneck.

    Server got no hardware changes. Even SELECT TOP(100) * FROM address is running slow, this means something else is taking the CPU, DISC, etc, and based on my investigation it looks to me it is the database and some queries... But I want to find which ones, and wheres the bottleneck of the server.

    SQL-server memory usage: 25.000 MB [25GB], and its locked (in Management studio, max memory usage option) at 26.555.788 +- 100 kb. CPU usage runs at 0% to 20-30%, sometimes 60-70%.

    My tries
    I have tried running this script:
    http://www.tarakizer.com/files/scrip...X_11032009.zip
    Which should defragment indexes on all tables in a specified database (no clue what this actually means, if it just removes all data that an index contains, and readds it based on data the database currently has? )...

    Running sp_updatestats
    Running sp_updatestats @resample = 'RESAMPLE';

    Enabling/Disabling TCP/IP, Piped Lines, etc..., in SQL Configuration Manager

    Restarting the server

    Calling these scripts: http://sqlfool.com/2009/01/index-clean-up-scripts/

    sp_configure and reconfigure the 'recovery interval'

    What more can I do before I call someone that has more knowledge of the database than me?
    Last edited by ManyTimes; 10-30-12 at 16:09.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Was the database backed up from and then restored to the same machine, or a different machine?

    use Profiler to capture a sample of the queries being run on the server. Make sure you include the reads, cpu, writes, and duration columns. This will become your baseline for measuring performance.

    Also, use perfmon to see if the CPU usage on the machine has maxed out, or if you are carrying big disk queues.

  3. #3
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    SameMachine
    '"The database" (I am actually talking about a complete restore of the whole server, including all databases, tools, programs, windows, etc.), but, yes, it is still the same "machine", although they are running on blades and virtual machines, so I cannot be sure to know where the data is actually located... But it is the exact same hardware running.

    Profiler
    I have used the sql profiler last week or so, and created a new template based off the TSL_Duration, that seemed correct to me, but could not find anything useful, maybe I need to run it for a long time and do some more filters... But ofc. all options can be changed by filtering and showing various columns... Giving it a bit more effort. Thanks

    PerformanceMonitor
    Performance monitor has also been used and CPU was fine, but theres so many options, so I just took some and hoped for the best... Which one should I actually monitor? Just trying and failing around? I've done that for ages, and it takes too much time...

    I've also looked at the ResourceMonitor [Task manager > Resource Monitor], which seem to use a lot of disc and CPU, memory is constant 25GB, due to the limit set in management studio.


    Thanks for your reply!

    Edit: moving the server to a new disc with 15000 RPM disc, instead of 10.000 RPM disc...

    Meanwhile:
    SQL profiler, one of the longest runs, both in read and CPU column:
    Reads: 30.310.649, CPU 106.549, caused by TextData: EXEC sp_cursorfetch 18015..., application: Our accounting program.

    What should I do now?
    Last edited by ManyTimes; 10-30-12 at 05:57.

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    In this sort of investigation, Profiler works best with a baseline, so you can reference back how often a particular query is run, and how many reads, and how much CPU it used in the past. Run it for an hour, and save the trace to a file, or best to a table in a database, so you can query it later.

    From the description, it sounds like you have disk troubles. Were all of the datafiles restored to the exact same paths? Or were the database files restored to fewer physical disks (i.e. consolidated)?

    To confirm, run the perfmon counter for SQL Server instance Buffer Manager : Page Life Expectancy. If there is a lot of memory pressure, this will be under 300. If it is consistently above 300, you may have a different problem.

    For the long running query, do a search in the profile trace for the ID number (18015...) to see if you can catch what the original query was. I expect it is some sort of report. If it is, we may be able to dismiss it, as a once in a while thing.

  5. #5
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    >>>"Profiler works best with a baseline"
    What is a baseline in SQL profiler? Or just talking about "baseline", which isnt an option nor a button, but "at least pick columns as time, reads, writes...." which defines the baseline...

    Heres a picture of SQL profiler after a 6-7 minutes:
    http://s10.postimage.org/v1i2lrtyf/Sql_Profiler.png

    I've created a template based on the T-SQL_Duration and changed columns and filters to my "needs", at least those needs I think I need...

    Ok, I'll run it for an hour.

    Had some disk issues, that is true.

    http://s16.postimage.org/vtikub2ar/Clipboard01.png

    The datafiles were just overwritten to the same path on restore, with the same amount of discs.

    Have moved the database to faster discs, 15.000 rpm instead of 10.000, but it is still slow.

    Another picture of a procedure running quite often, with a lot of writes and CPU:
    http://s16.postimage.org/tobpfvyoz/S...irrored_DB.png

    Did a search for 18015... and spotting a query that runs towards the accounting database seems to me its just a normal query something along these lines [this is what it does, exactly, but iwth different table names and columns...]:
    Code:
    SELECT TOP (1) table1.column, table2.column, ... FROM table1 JOIN table2 where table1 = @p1 and table2 = @p2 order by table1.ID,
    PerformanceMonitor Buffer manager - Page Life Expectancy, if I change the scale to 0.001, the blue bar is straight in the middle, blue "30"...oh, heres a picutre:
    http://s13.postimage.org/7u7rrjc8m/Clipboard02.jpg
    If I change the scale to 0.01, the Blue bar is at top and for all higher values, it remains at top. If I set it to 0.0001 [decreasing it] the bar is at bottom.

    Edit:
    It seems that moving the base to faster discs [higher RPM] helped a lot, it is not as slow anymore, but it is still slower than before, so one bottleneck has been found, but theres still more to it. Hm...

    Thanks a lot!
    Last edited by ManyTimes; 11-02-12 at 07:26.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    A baseline is a picture of how the server was doing on a good day. When you are looking for "something that has changed", you need to know what the system looked like before. For example, do you know if the shrinkdatabase procedure was being run before? How long did it take before? Is it really a problem?

    The page life expectancy (regardless of scale) looks to be over 25 million. disk access is not likely the problem here.

    It remains of course to find the actual bottleneck. Try running this query at the slowest times, and see what the most common type of wait is for the system.
    Code:
    select s.session_id, s.host_name, s.login_name, r.wait_type, r.wait_resource
    from sys.dm_exec_sessions s join
    	sys.dm_exec_requests r on s.session_id = r.session_id
    where s.session_id > 50
      and s.session_id <> @@spid

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Also, the query you show has no join condition?

  8. #8
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    Oh, thats the baseline you were asking for, well, I do not have any screens or anything that proves it was faster before, nothing to work with whatsoever.

    Page Life expectancy shown above is taken after "I" moved the server to new faster discs, so it was partially it.

    If the shrinkdatabase was running earlier, I do not know, and I cannot know.


    Query
    Code:
    Declare @p1 int, @p2 int...
    SET @p1 = <somenumber>
    SET @p2 = ...
    ...
    
    EXEC SP_cursorprepexec @p1 output, @p2 output, 
    N'@P1 nvarchar(5),@P2 nvarchar(5),@P3 nvarchar(21)',
    N'SELECT TOP 1 A.PROJID,A.RECID,B.TXT,B.RECID,B.CREATEDDATETIME,B.RECVERSION
     FROM DBTABLE1 A,DBTABLE2 B 
    WHERE (A.DATAAREAID=@P1) 
    AND ((B.DATAAREAID=@P2)
    AND ((B.PID=@P3) 
    AND (B.TRANSID=A.TRANSID))) 
    ORDER BY A.DATAAREAID,A.PROJID',
    @p5 output,@p6 output,@p7 output,N'tho',N'tho',N'F308235'
    select @p1, @p2, @p5, @p6, @p7
    Yes, remains to find the actual bottleneck.

    The query has no "JOIN" word in it, although it does use two tables so it does a join or whatever you want to call it.

    If I sort by writes, this is the query with most writes:

    Code:
    select 
    
    CONVERT(VARCHAR,CAST(bpool_committed *8  AS MONEY),1)AS [SIZE],
    
    bpool_committed,bpool_commit_target 
    
      from 
    
    sys.dm_os_sys_info
    Else; there is a lot of "EXEC SP_cursortfetch 180195602,2,1,1" the large number does change though.

    Thanks, I'll try the query once poeple are coming to work, still bit early.

    And I think I'll call for some assistance on this
    Last edited by ManyTimes; 11-01-12 at 05:04.

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    For the cursorfetch calls, the large number (1801...) is a statement ID, so that will change depending on who calls what query. I forget what the other numbers are. If I recall, one of them is a direction indicator, and another is the number of rows grabbed per call.

    I have no idea why they are converting bpool_committed to money, then varchar. It may be generated by perfmon, as I can't even see why an application would want to know what the bpool_committed value is. Even then, that query should not take up a lot of time. I generally go by the reads column, as that is a more reliable indicator of how much work a query does.

    The query you posted above does have a join condition (which is fortunate), it is just buried in the WHERE clause. It's not like we can modify the application or the queries it is sending, anyway.

    As a curiosity, when you run the query above in management studio, and check in profiler to see how it performs, does it perform similarly? If the reads remains about the same, but the duration is lower for management studio, then the application itself may be getting choked, and can't process the return data from the database quickly enough.

  10. #10
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    No idea why they convert to money myself...

    Yes, it does have a join as I've said myself, just not the word "join" in it.

    I did run the query above that begins with "EXEC SP_cursorprepexec @p1 output, @p2 output...", and it runs in management studio by 0.044 seconds, but then again it throws an error so it does not really run, error:
    The value of the parameter 'scrollopt' is invalid.

    And also: How can I see how many reads it takes besides running from management studio?

    In the profiler, is 1 row equal to "1 query"? So if the same query runs twice, the profiler creates two rows? I thought if the same query runs twice, the reads, duration and CPU adds up...?
    Last edited by ManyTimes; 11-02-12 at 11:39.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    In Profiler, one row is one event (whether it is in execution of a query, login, error, etc.). So, if you hit the execute button twice, you should see two lines. You may see more lines, if you are getting the execution plan included in the output.

    For the sp_cursorprepexec, I meant to run the query in the quotes. It will take a little digging to get the parameters sorted out, but the query itself should be relatively obvious. sp_cursorprepexec is an internal function that ay not translate well to Management Studio.

    Using the same tool for both measurements is the best way to compare the performance of a query.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •