Ask any Database Administrator (DBA) about database performance and they will tell you that tuning is a never ending task. Everyday environmental changes in the business present opportunities for performance improvement.

Performance and tuning considerations for making SQL statements run faster and more efficiently at the DBMS level are mandatory. However, effective solutions for performance issues are difficult to develop and maintain. What addresses a set of requirements in one area may have a negative effect on another, and the law of diminishing returns definitely applies to database tuning. You can put forth a great deal of time and energy into tuning a database with little to nothing to show for your effort. Luckily the latest SQL Server versions offer some great dynamic management views (DMV’s).  For example, the  sys.dm_io_virtual_file_stats can quickly unveil all database file I/O (input/output) statistics to identify which databases are “Hot” on Disk I/O and which are “Not”.

Typical database performance problems begin when the SQL Query Optimizer is unable to perform an Index Seek, either because there are no indexes or no useful indexes. When this happens, the SQL Server must execute a Table Scan operation on all of the data, looking for the records that meet the requirements of the query. For all but very small tables, this process can take a long time. Solid State Disk can optimize this process for you. The following video demonstrates a Table Scan speed of 3100 MB/sec (after 1 min, 05 sec) achieving the maximum throughput of a single DSI3000 solid state disk connected to a Unisys ES7000 server.

To download and view video demonstration, please click here.