Featured Article(s)
Tips for using SQL Server 2008 triggers
Here are some helpful tips to performance tune and optimize SQL Server 2008 triggers.
Part I: Oracle Data Warehousing: A conceptual overview
Oracle has made a commitment to support data warehousing applications with Oracle8i. Each new version of Oracle is an enhancement on the previous one. So what it data warehousing? What are its features. This article is a conceptual overview of data warehousing. The next few articles in this series will examine how Oracle has kept up its commitment to facilitating Data warehousing in the different versions of its applicaitons.
Featured Script
Create_Delete_Proc
This procedure will create a default delete proc based on a table name , id and data type… (read more)
On Index Defragmentation and Automation…
Steven wrote with their approach – "We find that certain indexes require defragmentation on a daily basis. These are primarily order tables and are heavily worked all day long. History is maintained it the tables so fragmentation has a big impact on performance for these.
As for other tables updated less frequently (still on a daily basis by less than up front incoming order tables) these I’ve found that weekly is a practical and efficient amount of maintenance when combined with custom scripts that monitor and address any deficiencies that might crop up.
I’ve included the custom script I use on all of our systems below to ensure that performance is always maintained at an optimum rate. It has served me well and fragmentation is something that I spend almost no time monitoring anymore." [SW: If anyone would like a copy of the script, let me know. I’ll get it posted to the site.]
Aron writes "We run a weekly maintenance plan Sunday morning @ 1:00 am that rebuilds all of our indexes. Our business accepts about 1,500 requests from our clients each week. This results in multiple records being created in multiple tables. There are also frequent updates to these records. Hence, our indexes do exceed the 30% level by the end of each week.
It is good practice to defrag indexes to keep the database tuned. However, we have not performed any benchmarks to measure the value of defrag vs. no defrag."
…and here’s Patrick’s experience – "We have a variety of systems that we support (120 servers/150 instances (2000/2005/2008)) and my experience shows that for many systems we are able to simply ignore the fragmentation levels because the performance of the system is not negatively impacted enough by the fragmentation to become a squeaky wheel. That said, however, some of our largest systems (0.5TB to 0.8TB in size with tables approaching 120GB) require outages and defragmentation every 6 months to keep performance at an acceptable level. We have a custom built SProc that we run on a weekly basis on the majority of the systems that sends us an email based report on the fragmentation levels of the indexes and also has internal logic to identify what actions should be taken based on best practices (defrag/rebuild/drop & recreate). This rarely gives us any grief and I feel we’ve effectively addressed fragmentation from a performance standpoint enterprise wide.
Yes, you will likely find some heavily fragmented tables/indexes here and there, but by and large we have very few performance complaints that can be tracked down specifically to fragmentation (I feel due to the process we have and use). 9 times out of 10 it’s an I/O bottleneck or someone running a report where it shouldn’t be run."