June 8, 2012 at 3:44 pm
I know there are many forums here which talk about sys.dm_io_virtual_file_stats DMV. As per MSFT the values are cumulative, however i do see the values to be decreasing also as shown below and yeah i am 110% sure server wasn't rebooted . ONLY thing i can think of is some how buffer was being cleaned up may be lack of memory, does anyone else know why does it show like this?
totalio_stall_insecsio_stall_read_insecsio_stall_write_insecsnum_of_reads
44918226713468
60326333922858
91828563216925
79313565812510
6241215029081
1423279114336383
385611142741196382
79821558221683
7491046458004
119395124246456
June 8, 2012 at 3:54 pm
Autoclose enabled?
Database restored?
DAtabase taken offline?
Database detached and attached?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 8, 2012 at 4:14 pm
GilaMonster (6/8/2012)
Autoclose enabled?Database restored?
DAtabase taken offline?
Database detached and attached?
Autoclose enabled? Not sure what this is
Database restored? Nope
DAtabase taken offline? Nope
Database detached and attached? Nope
June 8, 2012 at 4:15 pm
sqldba_newbie (6/8/2012)
GilaMonster (6/8/2012)
Autoclose enabled?Database restored?
DAtabase taken offline?
Database detached and attached?
Autoclose enabled? Not sure what this is
Database restored? Nope
DAtabase taken offline? Nope
Database detached and attached? Nope
By the way this is on SQL 2005
June 8, 2012 at 5:03 pm
sqldba_newbie (6/8/2012)
GilaMonster (6/8/2012)
Autoclose enabled?Database restored?
DAtabase taken offline?
Database detached and attached?
Autoclose enabled? Not sure what this is
The database setting autoclose.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2012 at 1:26 pm
GilaMonster (6/8/2012)
sqldba_newbie (6/8/2012)
GilaMonster (6/8/2012)
Autoclose enabled?Database restored?
DAtabase taken offline?
Database detached and attached?
Autoclose enabled? Not sure what this is
The database setting autoclose.
Wow , good to know , never really paid attention at this setting. It is set to 0, i think this is set to off. Could it be that sql server is running out of memory and buffers are being cleared?
June 11, 2012 at 3:24 pm
Any idea?
June 11, 2012 at 4:08 pm
If you are worried about memory being cleared out, you could look for signs of memory being paged out in the SQL server log. You might also be able to get a cached proc count and compare it to a count later on. If you see a lot of compiles that you would not normally see, or CPU spikes around any log error on paging out memory you'd have an indication. Buffer pool and procedure cache should drop to give some indication on memory pressure. Also depends on what counters and alters you have going, if any. If you track compilations per second you may be able to get a pattern off of it.
I have an alert to pick up on a few resource contentions but from 70 compilations instead of the 100 Brad pushed for here: http://www.sql-server-performance.com/2005/sql-server-performance-monitor-coutners/
select count(*) from sys.dm_exec_cached_plans <-- see if the plan counts dips for proc cache. I have typically seen this dump before going to page file, but the second thing is SQL log check for paging out memory to disk.
---
Set an alert to see where your compilation level is:
DATE/TIME:6/11/2012 5:00:34 PM
DESCRIPTION:The SQL Server performance counter 'SQL Compilations/sec' (instance 'N/A') of object 'SQLServer:SQL Statistics' is now above the threshold of 70.00 (the current value is 82.90).
COMMENT:If this number is over 100 for a self determined "chunk" of time, the overhead in compilation would be high for our server. Reference: http://www.sql-server-performance.com/2005/sql-server-performance-monitor-coutners/
JOB RUN:(None)
June 11, 2012 at 4:09 pm
Are running any reconfigure commands, either?
June 11, 2012 at 10:45 pm
matt.newman (6/11/2012)
If you are worried about memory being cleared out, you could look for signs of memory being paged out in the SQL server log. You might also be able to get a cached proc count and compare it to a count later on. If you see a lot of compiles that you would not normally see, or CPU spikes around any log error on paging out memory you'd have an indication. Buffer pool and procedure cache should drop to give some indication on memory pressure. Also depends on what counters and alters you have going, if any. If you track compilations per second you may be able to get a pattern off of it.I have an alert to pick up on a few resource contentions but from 70 compilations instead of the 100 Brad pushed for here: http://www.sql-server-performance.com/2005/sql-server-performance-monitor-coutners/
select count(*) from sys.dm_exec_cached_plans <-- see if the plan counts dips for proc cache. I have typically seen this dump before going to page file, but the second thing is SQL log check for paging out memory to disk.
---
Set an alert to see where your compilation level is:
DATE/TIME:6/11/2012 5:00:34 PM
DESCRIPTION:The SQL Server performance counter 'SQL Compilations/sec' (instance 'N/A') of object 'SQLServer:SQL Statistics' is now above the threshold of 70.00 (the current value is 82.90).
COMMENT:If this number is over 100 for a self determined "chunk" of time, the overhead in compilation would be high for our server. Reference: http://www.sql-server-performance.com/2005/sql-server-performance-monitor-coutners/
JOB RUN:(None)
Thanks. I am not worried about memory.As mentioned in original post, i am trying to find why number of reads/writes are not in ascending order. I see sometimes it is less than previous once, as per MSFT it is suppose to be cumulative.
June 12, 2012 at 3:09 am
I tried to look at your issue but the columns you posted do not match with those on the dm view, so, those values are the result of a process you failed to shared, the dm also return the data base I'd and the file I'd also missing in your post; would it be possible for you to post the query you are using to get the results you are getting?
Hope this helps,
Rock from VbCity
June 12, 2012 at 7:21 am
SELECT
DB_NAME(database_id) as [dbname],
CASE file_id
WHEN 1 THEN 'Data'
WHEN 2 THEN 'Log'
ELSE 'DATA'
END as [File_type] ,
num_of_reads as [Reads],
num_of_bytes_read/1024/1024 as [num_bytes_read_inMB],
io_stall_read_ms/1000 as [io_stall_read_insec] ,
io_stall_write_ms/1000 as [io_stall_write_insec],
io_stall/1000 as [total_io_stall_insec],
size_on_disk_bytes/1024/1024 as [size_on_disk_inMB)]
FROM sys.dm_io_virtual_file_stats(null, null)
WHERE DB_NAME(database_id) not in ('master','msdb','model','tempdb');
Can you reproduce the data results that you are seeing with maybe the above or provide your code with what at least file id/types. Still unclear as to what you are describing.
June 12, 2012 at 11:06 pm
my sincere apologies , after tracking back to the code( i didnt write this) i figured that the values are actual differential's from the baseline. Thanks.
June 14, 2012 at 7:43 am
sqldba_newbie (6/12/2012)
my sincere apologies , after tracking back to the code( i didnt write this) i figured that the values are actual differential's from the baseline. Thanks.
Which is actually the usual way to process this data for REPORTING, but if you store it this way you kinda lose the ability to do larger aggregates or timespans potentially. Gotta do a lot more math I think to unwind this. In any case, glad you found the answer you were looking for! I HATE not knowing!! :w00t:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply