:::: MENU ::::

Posts Categorized / SQL Server IO

  • Oct 01 / 2014
  • 0
dbDigger, Dynamic Management Views DMV, Monitoring and Analysis, SQL Server IO, T-SQL Scripts

Get number of reads and writes for each database on SQL Server

We can get the number of read and write operations for each database on our server. This information id being fetched by a DMV so analysis data depends upon up time of server.

 -- total I/O for each database  
 SELECT name AS 'Database Name'  
 ,SUM(num_of_reads) AS 'Number of Read'  
 ,SUM(num_of_writes) AS 'Number of Writes'   
 FROM sys.dm_io_virtual_file_stats(NULL, NULL) I  
 INNER JOIN sys.databases D   
 ON I.database_id = d.database_id  
 GROUP BY name ORDER BY 'Number of Read' DESC;  
  • Feb 24 / 2012
  • 0
Backup and Restore, dbDigger, SQL Server Error messages, SQL Server IO, System Administration, Windows Server

Database backup and restore failed on mapped network drive

During my job at National Database and Registration Authority of Pakistan (NADRA), i was accustomed to create database backups directly on mapped network drives. We have a domain configured at NSRCs with SQL Server 2000. Fore servers with single disk, it becomes very efficient by preventing the local server disk IO. Now while working with SQL Server 2005 installed on windows server 2003 work group. i was required to create an adhoc backup during peak hours. For optimized IO i tried to use mapped network drive for creating backup on it directly. But surprisingly mapped network drive was neither available in SSMS backup location explorer nor through T-SQL backup statement. Error message was being generated.

Database backup and restore on mapped network drive

Same statement was working successfully for any location other than mapped network drive. Keeping in view my previous experience with SQL Server 2000 on domain i was not expecting any problem in this task. However after some time i realized that network drive is not available as backup device.

Next option was BOL and Google. There i came to know that mapped network drives are available for backups only when your SQL Server service is running under domain account that has access to mapped network drive.


Without a privileged domain account as start up account of SQL Server service, mapped network drives cannot be accessed from within SQL Server context for backup or restore process.

  • Oct 27 / 2008
  • 0
Buffer, dbDigger, Performance Tunning and Optimization, SQL Server IO

SQL Server I/O operations in buffer

SQL Server deals the log operations for read and write requests in an entirely different way. Here is a comparison for both types handling in buffer.

As a read request is submitted to SQL Server, first it is looked in buffer cache of SQL Server. If data is found in buffer cache then it is retrieved from there and if not found in buffer cache then it is retrieved from storage engine to buffer cache and provided to requester from buffer cache.
If requested data is found in buffer cache then it is called buffer hit.
If data is not found in buffer cache and storage engine is concerned then it is called buffer miss.
The ratio of buffer hits to total buffer requests is called buffer cache hit ratio.
As much as buffer cache hit ratio is close to 100 %, you have got as good performance.

In case of write operation for SQL Server database, data is always first written to buffer cache. If data is modified first it will be modified in buffer cache and if not present in buffer then it will be retrieved from disk for modification in buffer. When change is complete and COMMIT is performed a LOG WRITE process will write changes in log file and COMMIT is complete now. Till now the changes are not written to disk. Changes will be applied to data on disk at a later time either by LAZY WRITER process or CHECKPOINT process. Unlike read operation during all this process to write/modify data on disk user session will not wait.

So read performance is more important to consider than write performance.

Click here to read all posts related to SQL Server storage systems and I/O operations

  • Oct 27 / 2008
  • 0
dbDigger, Performance Tunning and Optimization, SQL Server IO, Storage

Sequential and Random I/O for Databases

Sequential I/O is operation in which adjacent data is accessed on a disk drive. Track to track seek is performed and thus greater throughput is provided.

And in case of random I/O, disk head reads data scattered on various parts of disk. Different tracks are read that are not adjacent, so random movement of head causes degrade in performance.

Keeping in view the major characteristic of sequential I/O and random I/O, it is important to design database disk storage in such a way that maximum sequential I/O may be performed. Transactional log is a major example of sequential I/O in SQL Server environment. You should place a heavily used log file on one disk. And it is good approach to use just only one default log file. If you place multiple log files on same disk the access type will be changed from sequential to random I/O. Practically it is not possible to achieve all I/O as sequential I/O in a server based environment. Clients request different operations for different pieces of data. So the only solution is to map your data on multiple disks so that random access should be minimized.

Click here
to read all posts related to SQL Server storage systems and I/O operations

Consult us to explore the Databases. Contact us