SQL Server Performance

Slow query with temporary table

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by hbonvin, Sep 28, 2009.

  1. hbonvin New Member

    Hello,

    We have a something special with the logical reads on a update query.

    We prepare some records (512) into a physical temporary table. The records are identified by the query_id. When we execute the update on the first set of records the execution time is Ok. But with the second set of records. The execution is very slow and the reads are huge.

    Both data sets are feed with the same data.

    Could you help me please?



    Query :

    UPDATE ADCOSDT set Title_code_no = a.ADp7A, city_ennumerator_key = ad.ADkdA, city = c1.ADkiA, Titre_description = t1.ADfmA, Titre_description_traduit = td1.ADfmA, Titre_long_description_traduit = td1.ADFQA, Titre_complement = m.ADp8A, Secondary_Title_code_no = m.ADp9A, Secondary_Titre_description = t2.ADfmA, Secondary_Titre_description_traduit = td2.ADfmA, Secondary_Titre_long_description_traduit = td2.ADFQA, Additional_main_contact_text = m.ADooA, Post_box = ad.ADpjA , home_number_code = ad.ADplA, Street_extention = ad.ADlkA, Zip_code = c1.ADkeA, Country_code = c1.ADfra, Country_short_descr = ctr.ADfsA, State_code = c1.adg1a, State_short_descr = stt.adgxa, Phone_national_code = ad.ADpmA, Phone_regional_code = ad.ADpnA, Phone_number = ad.ADpoA, Mobile_national_code = ad.ADppA, Mobile_regional_code = ad.ADpqA, Mobile_number = ad.ADprA, Fax_national_code = ad.ADpsA, Fax_regional_code = ad.ADptA, Fax_number = ad.ADpuA, Contact_Qualifier = a.ADfoA, Street = ad.ADljA, home_number_alpha = ad.ADpkA, Real_Name = a.ADpbA, Real_First_name = a.ADpaA, MC_Name = a.ADpbA, MC_First_name = a.ADpaA, MC_Complementary_name = a.ADpcA, MC_Zip_code = c1.ADkeA, MC_city = c1.ADkiA, CD_Lang_Pers = a.AD2rfA, CD_Lang_Speak = a.AD2rgA, Birth_Creation_date = a.ADpfA from ADCOSDT s inner join adconta a on s.no_contact=a.ADona left outer join admaco m on a.adona = m.adona left outer join adti t1 on a.adp7a = t1.adfla left outer join adtid td1 on a.adp7a = td1.adfla and td1.SE42A = a.AD2rfA left outer join adti t2 on m.ADp9A = t2.adfla left outer join adtid td2 on m.ADp9A = td2.adfla and td2.SE42A = a.AD2rfA inner join adad ad on ad.ADelA = a.adona and ad6ma = 'O' left outer join adci c1 on ad.ADkdA = c1.ADkdA left outer join adcountr ctr on c1.ADfra = ctr.ADfrA left outer join adst stt on ctr.adfra = stt.adfra and c1.adg1a = stt.adg1a where s.query_id = 4 ;



    First execution IO

    Table 'ADCOSDT'. Scan count 1, logical reads 1123, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADMaCo'. Scan count 0, logical reads 1577, physical reads 504, read-ahead reads 258, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADConta'. Scan count 0, logical reads 1577, physical reads 18, read-ahead reads 508, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADAd'. Scan count 1, logical reads 6755, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADTi'. Scan count 2, logical reads 4, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADTid'. Scan count 2, logical reads 4, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADCi'. Scan count 1, logical reads 256, physical reads 1, read-ahead reads 280, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADCountr'. Scan count 1, logical reads 7, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADSt'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 438 ms, elapsed time = 759 ms.


    Second execution IO
    Table 'ADCOSDT'. Scan count 1, logical reads 1123, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADSt'. Scan count 0, logical reads 1024, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADCountr'. Scan count 0, logical reads 1024, physical reads 2, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADCi'. Scan count 0, logical reads 1024, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADTid'. Scan count 0, logical reads 2048, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADTi'. Scan count 0, logical reads 2048, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADMaCo'. Scan count 0, logical reads 1536, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADConta'. Scan count 0, logical reads 1536, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'ADAd'. Scan count 1, logical reads 3458560, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:
    CPU time = 101610 ms, elapsed time = 106243 ms.


  2. FrankKalis Moderator

    Welcome to the forum!
    What do the execution plans say? Do you clear the cache after each execution? How many rows are in the involved tables?
    ...btw, can you please remove the Office tags from your question? They make your post very hard to read.
  3. hbonvin New Member

    I don't clear the cache after the exections and the main tables have :

    ADCOSDT : 1024 rows (2x512)
    Adad : 281961 rows
    ADConta 281665 rows
    ADMaco 281665 rows

    When I execute the clear of the cache before the both update queries, the access of the table are in the same order.

    Is the access plan build according to the data stored into the memory of the server ?

    Thank you for your help.
  4. moh_hassan20 New Member

    It seems that two different execution plans are used, especially i find :
    - worktable in first execution and not in second one.
    - the order of join is different.
    if that query is dynamic , it may have different execution plan in the cache buffer, and the best is parametrized query.
    here is the problem in the second execution:
    the second execution:
    [quote user="hbonvin"]Table 'ADAd'. Scan count 1, logical reads 3458560, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.[/quote]
    But first execution is :
    [quote user="hbonvin"]
    Scan count 1, logical reads 6755
    .[/quote]
    The question is: from where these 3+ million page come from?
    can you post from the execution plan the join type (Loop, merge ,hash) for both execution
    To work around the problem:
    1) If we considered the first query is the best you can:
    Re-write the query and let the join order as in the first exeecution , and use FORCE ORDER hint
    2) if you use dynamic sql to compose the where condition , use parameterized query
    so, The question is: from where these 3+ million page come from ?[:D]
  5. hbonvin New Member

    The update queries are the same based on the same data stored into the table ADCOSDT. When I display the execution plan, all access are on indexes, only the access to ADCOSDT is a table scann.
    We have done the test with the parameter FORCE ORDER. The access is now in the same order but the second query read every time +3 million.
    We have done a new installation of the database on an other server. The queries are on the new server without the +3 million reads. The first diffrence that we see is the memory options.
    first slow server :
    min server memory : 128 Mb
    max server memory : 512 Mb
    second good server :
    min server memory : 0 Mb
    max server memory : 2147483647 Mb
    It's possible that the settings of the sever memory could collapse the perfromance ?
    Do you knonw a rule to set the best memory settings ?
    Thank you for your help.
  6. moh_hassan20 New Member

    [quote user="hbonvin"]It's possible that the settings of the sever memory could collapse the perfromance ?[/quote]
    Sure it is.
    in first configuration , 512MB is the upper limit for the buffer pool which slow performance and let sql server swap cached data to the disk on the page file(swap file).
    in second configuration , dynamic , the upper limit is the available memory.
    What is the server configuration of the old and new server: memory., database size.
    can you run dbcc memorystatus on the slow server ,during execution of the query , and post the results.
  7. hbonvin New Member

    The old server (slow server):
    Logical CPU : 2
    Physical CPU : 1
    Physical Memory (MB) : 3071
    File Name Total Size in MB Available Space In MB
    XCRMDTA 914 22.875000
    XCRMDTA_log 1 0.828125
    The new server
    Logical CPU : 1
    Physical CPU : 1
    Physical Memory (MB) : 2047
    File Name Total Size in MB Available Space In MB
    XCRMDTA 1003 60.125000
    XCRMDTA_log 194 183.875000
    Die DBCC-Ausführung wurde abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.
    Memory Manager KB
    ------------------------------ --------------------
    VM Reserved 1684248
    VM Committed 322640
    AWE Allocated 0
    Reserved Memory 1024
    Reserved Memory In Use 0

    (5 Zeile(n) betroffen)

    Memory node Id = 0 KB
    ------------------------------ --------------------
    VM Reserved 1680088
    VM Committed 318632
    AWE Allocated 0
    MultiPage Allocator 18400
    SinglePage Allocator 160176

    (5 Zeile(n) betroffen)

    MEMORYCLERK_SQLGENERAL (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 4352
    MultiPage Allocator 2328

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLBUFFERPOOL (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 1654624
    VM Committed 293896
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 0
    MultiPage Allocator 400

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLQUERYEXEC (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 32
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLOPTIMIZER (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 696
    MultiPage Allocator 72

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLUTILITIES (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 120
    VM Committed 120
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 344
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLSTORENG (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 3840
    VM Committed 3840
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 4712
    MultiPage Allocator 360

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 208
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLCLR (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLSERVICEBROKER (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 112
    MultiPage Allocator 192

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLHTTP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SNI (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 16

    (7 Zeile(n) betroffen)

    MEMORYCLERK_FULLTEXT (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLXP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_HOST (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SOSNODE (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 26408
    MultiPage Allocator 13824

    (7 Zeile(n) betroffen)

    MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 24
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_OBJCP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 2752
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_SQLCP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 76744
    MultiPage Allocator 824

    (7 Zeile(n) betroffen)

    CACHESTORE_PHDR (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 5688
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_XPROC (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 32
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_TEMPTABLES (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_NOTIF (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_VIEWDEFINITIONS (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_XMLDBTYPE (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_XMLDBELEMENT (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_XMLDBATTRIBUTE (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_STACKFRAMES (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 0
    MultiPage Allocator 8

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERTBLACS (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 216
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERKEK (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERDSH (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERRSB (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERREADONLY (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 32
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_BROKERTO (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 8
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_EVENTS (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 16
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    CACHESTORE_SYSTEMROWSET (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 1640
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_SCHEMAMGR (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 30728
    MultiPage Allocator 192

    (7 Zeile(n) betroffen)

    USERSTORE_DBMETADATA (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 2672
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_TOKENPERM (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 56
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_OBJPERM (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 912
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    USERSTORE_SXC (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 24
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    OBJECTSTORE_LBSS (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 88
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    OBJECTSTORE_SNI_PACKET (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 248
    MultiPage Allocator 48

    (7 Zeile(n) betroffen)

    OBJECTSTORE_SERVICE_BROKER (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 0
    VM Committed 0
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 256
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    OBJECTSTORE_LOCK_MANAGER (Total) KB
    ---------------------------------------------------------------- --------------------
    VM Reserved 2048
    VM Committed 2048
    AWE Allocated 0
    SM Reserved 0
    SM Commited 0
    SinglePage Allocator 960
    MultiPage Allocator 0

    (7 Zeile(n) betroffen)

    Buffer Distribution Buffers
    ------------------------------ -----------
    Stolen 4626
    Free 15913
    Cached 15396
    Database (clean) 0
    Database (dirty) 65
    I/O 0
    Latched 0

    (7 Zeile(n) betroffen)

    Buffer Counts Buffers
    ------------------------------ --------------------
    Committed 36000
    Target 45591
    Hashed 65
    Stolen Potential 42237
    External Reservation 0
    Min Free 64
    Visible 45591
    Available Paging File 248993

    (8 Zeile(n) betroffen)

    Procedure Cache Value
    ------------------------------ -----------
    TotalProcs 1123
    TotalPages 10755
    InUsePages 248

    (3 Zeile(n) betroffen)


    Global Memory Objects Buffers
    ------------------------------ --------------------
    Resource 464
    Locks 123
    XDES 14
    SETLS 2
    SE Dataset Allocators 4
    SubpDesc Allocators 2
    SE SchemaManager 3612
    SQLCache 166
    Replication 2
    ServerGlobal 26
    XP Global 2
    SortTables 2

    (12 Zeile(n) betroffen)


    Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 20734
    Maximum (Buffers) 20734
    Limit 20734
    Next Request 0
    Waiting For 0
    Cost 0
    Timeout 0
    Wait Time 0
    Last Target 21825

    (11 Zeile(n) betroffen)

    Small Query Memory Objects Value
    ------------------------------ -----------
    Grants 0
    Waiting 0
    Available (Buffers) 1091
    Maximum (Buffers) 1091
    Limit 1091

    (5 Zeile(n) betroffen)

    Optimization Queue Value
    ------------------------------ --------------------
    Overall Memory 299261952
    Target Memory 162021376
    Last Notification 1
    Timeout 6
    Early Termination Factor 5

    (5 Zeile(n) betroffen)

    Small Gateway Value
    ------------------------------ --------------------
    Configured Units 8
    Available Units 8
    Acquires 0
    Waiters 0
    Threshold Factor 250000
    Threshold 250000

    (6 Zeile(n) betroffen)

    Medium Gateway Value
    ------------------------------ --------------------
    Configured Units 2
    Available Units 2
    Acquires 0
    Waiters 0
    Threshold Factor 12

    (5 Zeile(n) betroffen)

    Big Gateway Value
    ------------------------------ --------------------
    Configured Units 1
    Available Units 1
    Acquires 0
    Waiters 0
    Threshold Factor 8

    (5 Zeile(n) betroffen)

    MEMORYBROKER_FOR_CACHE Value
    -------------------------------- --------------------
    Allocations 15400
    Rate 0
    Target Allocations 30558
    Future Allocations 0
    Last Notification 1

    (5 Zeile(n) betroffen)

    MEMORYBROKER_FOR_STEAL Value
    -------------------------------- --------------------
    Allocations 4620
    Rate 0
    Target Allocations 19778
    Future Allocations 0
    Last Notification 1

    (5 Zeile(n) betroffen)

    MEMORYBROKER_FOR_RESERVE Value
    -------------------------------- --------------------
    Allocations 0
    Rate 0
    Target Allocations 23291
    Future Allocations 8133
    Last Notification 1

    (5 Zeile(n) betroffen)

    Die DBCC-Ausführung wurde abgeschlossen. Falls DBCC Fehlermeldungen ausgegeben hat, wenden Sie sich an den Systemadministrator.

    (512 Zeile(n) betroffen)
    Thank you
  8. satya Moderator

    What is the physical memory set on SQL Server?
    WHat is the size of TEMPDB?
    http://sqlserver-qa.net/blogs/perftune/archive/2009/07/28/5269.aspx fyi.
    It is difficult to estimate the tempdb space requirement for an application. This section describes the general methodology for estimating the space requirement. These methods are not precise. It requires experience and experiment to gain a satisfactory result. We recommend that you always have a safety factor of about 20% more space. Space estimates must also allow for the future growth of data in the application.
    Applications usually send many queries to SQL Server. If queries are sent to the server serially, the peak tempdb space requirement is determined by the query that requires most of the tempdb space. If queries are executed at about the same time, the total tempdb space requirement is the sum of the requirement of all queries.
    SQL Server has a cost-based query optimizer. It chooses a query plan that has the lowest execution cost but it ignores the implications of this plan on tempdb. As a result, the tempdb space requirement for a query may vary, depending on the plan. In some cases, the best plan according to the query optimizer may require more tempdb space than a less efficient plan would. Also, when an application is upgraded from SQL Server 2000, a plan change may require more space in tempdb.
  9. moh_hassan20 New Member

    I found the numbers of buffer pool are normal, and committed buffer pool is 315MB (which is less 512MB of your max setting).
    i suggest for old server:
    - set min /max memory with the defaults ( to use all available memory).
    -rebuild statstics / indexes
    - force database PARAMETERIZATION (if you use adhoc / non parametrized queries)
    you may run DBCC DROPCLEANBUFFER and DBCC CLEARPROCCACHE (just for test only, it is avoided in production server) before the two queries to be not biased to old compiled plan.
    check and feedback

Share This Page