March 3, 2005 at 4:01 pm
Looking at Performance monitor I noticed that “% Processor Time” Is very low. When Running complex quires both “Pages/Sec” And “Avg. Disk Query Length” Rise very close to 100%.
So I upgraded Memory from 1 GB to 3 GB (After Discussing with vendor who felt that was the appropriate action).
Installed the memory and the queries seemed to run much faster. Performance monitor show the 2 problem counters running at about 50% when the complex quires are kicked off.
The Problem is that I have one SP that calls an update query and a delete query. This query causes my “% Processor Time” to go up to around 60% (which it never use to do) and the SP takes 3 times as long to complete.
Any Ideas?
Thank You In Advanced
March 4, 2005 at 3:16 am
Try to do a reindex, update stats and then recompile the SP's
March 4, 2005 at 7:52 am
Check your total server memory vs. your target memory. If total isn't close to target, then you likely don't have memory pressure problems.
select
total_mem_mb = a.cntr_value / 1024,
target_mem_mb = b.cntr_value / 1024 ,
pressure_ratio = convert(decimal(18,2),100 *
(convert(decimal(18,2),a.cntr_value) / b.cntr_value))
from
master..sysperfinfo a,
master..sysperfinfo b
where
a.object_name = 'SQLServer:Memory Manager' and
b.object_name = a.object_name and
b.counter_name like 'Target Server Memory%' and
a.counter_name like 'Total Server Memory%'
March 4, 2005 at 8:04 am
Try to do a reindex, update stats and then recompile the SP's
^---- I will Try that tonight. I am assuming that Reindexing the table may take a while.
Total | Target | Pressure
1640 | 1640 | 100.00
Is That Good or Bad, Why Or Why Not?
Thanks For the replies I will Let everyone know how the Reindexing works.
March 4, 2005 at 8:32 am
Check out DBCC INDEXDEFRAG in books on-line. You can do it while the server and DB's are on-line, and hopefully with little effect on the server.
March 4, 2005 at 8:47 am
DBCC INDEXDEFRAG
Is Running Now.
March 4, 2005 at 12:52 pm
What are your metrics from the query?
March 4, 2005 at 3:30 pm
After running the Query You Suggested I got:
Total_Mem_MB = 1640
Target_Mem_MB = 1640
Pressure_Ratio = 100.00
Is that the information you are looking for?
March 7, 2005 at 7:10 am
Have you manually set your max server memory config parm? If so, then I'd let SQL Server have a try at dynamic allocation (and "unset" that parm). If not, then your server may be memory constrained as your target memory amount (the limit SQL Server can use) equals the total, which hints at memory pressure. In other words, SQL Server wants more RAM but none is available.
Other stats to verify this include the buffer cache hit ratio, and code plan ratios. Also, the page life expectancy metric is a good one to view as it tells you if your data cache is being flooded with pages from table scans. Readings of 300 or less may indicate a cache that's 'thrashing' and artificially increasing your buffer cache size:
select
cntr_value
from
master..sysperfinfo
where
object_name = 'SQLServer:Buffer Manager' and
counter_name = 'Page life expectancy'
March 7, 2005 at 10:32 am
No, I have not set up the max server memory parameter. The Server Properties, Memory Tab (In EM) Is set to the Following:
Dynamically Configure SQL Server Memory = TRUE
Minimum (MB) = 0MB
Maximum (MB) = 3071
Use a fixed memory size = False
Reserve physical memory for SQL server = False
Minimum query memory (KB) = 1024
Configured Values = True
Running Values = False
After Running the Query You Suggested I came up with a result of 35,505 well above the 300 mark. I also Ran The query during times that large queries were executing and got results in the 18,000 – 19,000 Range.
Buffer cache hit ratio = 2,983 (Normal Usage)
Buffer cache hit ratio = 3,309 (During Large Query Execution)
(My Pressure Ratio Was At 99.56% This Morning)
Schumacr I really appreciate you taking the time to help me out with this.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply