DNS Virus
A DNS virus has been released for a number of months. The USA Federal Bureau of Investigation has been protecting the Internet from this virus by hosting a number of servers that are being take offline today for the first time after the virus was discovered. This virus can impact many kinds of devices that may be connected to the Internet. If you would like more information go to www.dcwg.org.
Database Statistics
In a recent conversation with a colleague we were discussing key factors in database performance. We talked about the value of appropriate database normalization, maintaining appropriate indexes and even defragmenting data.
High on the list was the maintenance of good database statistics. For the accidental DBA or SQL Server novice, SQL Server uses database statistics to determine the least cost method when performing your queries. Statistics help the SQL Server optimizer determine what index to use or exclude when joining, sorting or filtering data.
SQL Server has three different kinds of joins tuned for different kinds of scenarios. Statistics are also used to help determine the kind of join best suited for the query it is evaluating. OK, statistics are a good thing…so now what?
Now you need to keep your statistics up to date. By default, the later versions of SQL Server create and update statistics real time. If it determines a missing statistic will help determine the best query plan, a new statistic will be generated and maintained. For small databases this technique works fine. As your data grows you can imagine the impact of constantly creating and maintaining statistics.
Frequently statistics maintenance will be turned off. Creating new statistics is done manually by reviewing suggested statistics using DMV views and functions. DMVs are also used to automate re-generation of statistics during off peak hours. I have scheduled jobs that review and update indexes and statistics that are too far out of data, or too fragmented every 24 hours. By only updated tables that exceed my minimum threshold I am able to complete these maintenance tasks within an acceptable window.
So, to summarize:
If your database is small enough turn on the auto create and auto update statistics configuration settings for your database. You can leave these settings until your database performance begins to lag. Even then, maintaining statistics may not be the cause for slower database performance.
If your database is getting larger, and you have the time to learn DMVs you can create your own job to maintain your statistics during off-peak hours.
Tomorrow I’ll provide some links and a book suggestion for automating some of these tasks.
If you’d like to share your comments send them to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
UNIX for DBAs (Part 4)
UNIX is one of the most popular operating system for running database management systems. Of course, Windows is popular, too, as are z/OS and even OS/400, not to mention Linux (but that is a variant of UNIX). At any rate, UNIX is ubiquitous in the realm of database processing and it is important that DBAs know the basics of UNIX in order to effectively manage the databases on that platform. This article offers up a short tutorial on the basic UNIX operating system commands and features that are most important for DBAs to understand.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)