http://www.sqlmag.com/authors/author/author/3947067/rsscomment/3947067en-USFri, 25 May 2012 10:20:54 GMTFri, 25 May 2012 10:20:54 GMThttp://www.sqlmag.com/article/performance/boost-performance-with-parallel-processing#commentsAnchorThu, 01 Sep 2011 06:55:06 GMT
Parallelization for queries (SELECT) is covered quite well by the SQL engine itself, but when it comes to large volume data modifications (UPDATE, INSERT, DELETE), the standard engine does parallelize towards best use of all available resources (disk, multiple cpu-cores, etc.). Therefore you may have a look into the approach of SQL Parallel Boost at http://www.ibax.ch/-IBX-/plain.simpleimagetitletextlinklist.en.solutions.products.parallelboost/default.aspx This approach can also be used to execute multiple SQL statements in parallel. A purely SQL engine related parallelisation solution takes advantage of minimized complexity and has no 'external' components like SSIS involved, Furthermore it's the best performing solution regarding task splitting and synchronization, as it hasn't potential connection and communication overhead. The overall performance gain thru parallelisation with SQL Parallel Boost is up to 10 ! In case you don't want to rebuild your own solution, SQL Parallel Boost provides a self-contained pure T-SQL based solution, which can be easily embedded in existing applications and ETL process tasks. SQL Parallel Boost improves large volume data modification operations up to factor 10 (!). The free 'Community Edition' of SQL Parallel Boost can be downloaded at http://sqlparallelboost.codeplex.com]]>michael.broennimann@ibax.chThu, 01 Sep 2011 06:55:06 GMThttp://www.sqlmag.com/article/performance/boost-performance-with-parallel-processing#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/getting-to-know-wait-stats#commentsAnchorFri, 19 Aug 2011 14:20:08 GMT
For some reason the links for the table 1 and figure 1 show a windows live onecare installation........]]>toltecmanFri, 19 Aug 2011 14:20:08 GMThttp://www.sqlmag.com/article/sql-server-2005/getting-to-know-wait-stats#commentsAnchorhttp://www.sqlmag.com/article/sql-server/fine-tuning-plan-reuse-129462#commentsAnchorSat, 30 Jul 2011 16:02:01 GMT
Harry, Yes the plans are memory resident only and will be lost if you restart SQL Server. However restarting SQL Server should be a rare occurance. Andy]]>gunneykSat, 30 Jul 2011 16:02:01 GMThttp://www.sqlmag.com/article/sql-server/fine-tuning-plan-reuse-129462#commentsAnchorhttp://www.sqlmag.com/article/sql-server/fine-tuning-plan-reuse-129462#commentsAnchorTue, 14 Jun 2011 06:59:23 GMT
Andy, Thank for the information. My point is that the plan is stored in cache in stead of a location in the database. Whenver Sql Server has been restarted the information is lost. Or am i wrong? Harry]]>hvdtolTue, 14 Jun 2011 06:59:23 GMThttp://www.sqlmag.com/article/sql-server/fine-tuning-plan-reuse-129462#commentsAnchorhttp://www.sqlmag.com/article/performance-tuning/demystify-minimal-logging-129916#commentsAnchorFri, 03 Jun 2011 08:28:37 GMT
Chris, You are correct in that it is chopped off at the end but if you click on the graph it will enlarge the image so you can read it much better. Andy]]>gunneykFri, 03 Jun 2011 08:28:37 GMThttp://www.sqlmag.com/article/performance-tuning/demystify-minimal-logging-129916#commentsAnchorhttp://www.sqlmag.com/article/performance-tuning/demystify-minimal-logging-129916#commentsAnchorTue, 24 May 2011 07:59:16 GMT
Thanks that’s very helpful, apparently I’ve been logging more than I intended. FWIW the image is a little hard to read and the difference row is chopped off on the browser I’m using - but I can subtract, so no worries - thanks again!]]>Chris ComptonTue, 24 May 2011 07:59:16 GMThttp://www.sqlmag.com/article/performance-tuning/demystify-minimal-logging-129916#commentsAnchorhttp://www.sqlmag.com/article/sql-server/fine-tuning-plan-reuse-129462#commentsAnchorFri, 15 Apr 2011 08:39:39 GMT
Harry, The only suggestion to tweak code is when you make calls that do not allow for plan reuse and cannot be overcome with the two options listed. It is not a matter of getting a better plan it is all about reusign the existing ones. Getting a better plan is a whole different story. But if you don’t get plan reuse then you waste a lot of time & resources compiling new plans each time you make the call to the database. At present SQL Server doesn’t have the option to change any of the existing plans in the cache other than to mark them for recompile. The point here is that is you are not getting plan reuse you can turn on one of the two options listed to try and get better plan reuse with the FORCE PARAMETERIZATON option or to minimize the impact of the plans that don’t get reused with the OPTIMIZE FOR ADHOC WORKLOAD option. Neither of those options requires any changes in code. I hope that is more clear. Andy]]>gunneykFri, 15 Apr 2011 08:39:39 GMThttp://www.sqlmag.com/article/sql-server/fine-tuning-plan-reuse-129462#commentsAnchorhttp://www.sqlmag.com/article/sql-server/fine-tuning-plan-reuse-129462#commentsAnchorMon, 04 Apr 2011 12:06:52 GMT
I’m a DBA for Sql Server and Oracle, so i am able to compare. I this matter, Microsoft can learn from Oracle. Since their latest version 11g, Oracle stores each executed plan in the database. Whenever the same query comes along, the same plan is is being used. A nightly tuning task, looks for better plans from queries executed during the day, and stored it in the database, which can be used next time the query runs. This is much more effecient then tweeking the code, as this article suggests. Harry ]]>hvdtolMon, 04 Apr 2011 12:06:52 GMThttp://www.sqlmag.com/article/sql-server/fine-tuning-plan-reuse-129462#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorFri, 11 Mar 2011 08:49:30 GMT
Great article! You covered some key areas that frequently get missed, any one of which could be "the problem". Also nice to see the Waits & Queues whitepaper reference at top... sometimes I think people overlook it because is says 2005 -- big mistake!]]>GONZALEZFri, 11 Mar 2011 08:49:30 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorThu, 10 Mar 2011 19:39:21 GMT
Great article, thanks for the insight]]>BragdonThu, 10 Mar 2011 19:39:21 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/sql-server/is-tempdb-affecting-your-day-to-day-sql-server-performance-#commentsAnchorMon, 28 Feb 2011 16:16:34 GMT
SET NOCOUNT ON;

USE tempdb;
GO
--procedure of InstantDoc ID 101993
IF OBJECT_ID(’dbo.TestCaching’, ’P’) IS NOT NULL
DROP PROC dbo.TestCaching;
GO

CREATE PROC dbo.TestCaching @tbl_id INT OUTPUT
AS
CREATE TABLE #T1(n INT, filler CHAR(2000));
INSERT INTO #T1 VALUES
(1, ’a’),
(2, ’a’),
(3, ’a’);
SELECT n, filler FROM #T1;

SELECT @tbl_id = OBJECT_ID(’tempdb..#T1’)
SELECT @tbl_id AS T1_temp_table_id

--explicitly truncate and drop the table
TRUNCATE TABLE #T1
DROP TABLE #T1
GO


--:execute in another window
DECLARE @tbl_id INT
EXEC dbo.TestCaching @tbl_id = @tbl_id OUTPUT

--the same temporary table is used for repeated executions, although the temporary table is explicitly dropped and truncated
SELECT name, * FROM tempdb.sys.objects WHERE object_id = @tbl_id
--:execute in another window]]>KoukoulidisMon, 28 Feb 2011 16:16:34 GMThttp://www.sqlmag.com/article/sql-server/is-tempdb-affecting-your-day-to-day-sql-server-performance-#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorSat, 22 Jan 2011 13:39:33 GMT
Davison as always it depends :). But if you only have 6 disks to work with for all things then you may indeed be better off with everything on 1 array vs. 2 or 3 smaller ones. But this is more true as the number of spindles increases. However the more log activity you do the more likely you will see benefit by separating the log files. With only 6 disks and just 150GB db it probably wont matter too much either way. Hopefully you have enough Memory to keep the most active part of the db in cache and negate the physical IO from the data reads. This wont always work out as the data sets get larger though. But the bottom line is a lot depends on the workload and that will vary for each application.]]>KellySat, 22 Jan 2011 13:39:33 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorWed, 19 Jan 2011 06:42:59 GMT
I have always seperated logs, data & tempdb with a typical small server config being 3 sets of 2 x 300Gb disks as RAID1+0.
However I recently had the opportunity to perform some disk testing and just for the hell of it created one big logical disk from 6 x 300Gb RAID1+0 and put everything on this one disk.
A 150Gb OLTP database was restored and a number of tests were performed such as backups, restores, update stats, index rebuilds, DBCC, data inserts & deletes and a number of overnight reports, some of which are heavy users of tempdb.
I was quite suprised to find that everything ran significantly faster with everything all on the one big disk (eg: DBCC was over 30 mins quicker). The only exception being the reports which were heavy users of tempdb and these were just a few seconds slower than when using seperate disks.
I’m not in a position to simulate real user activity but from the above tests it looks like the benefit of having ALL the data spread over 6 disk spindles (rather than seperated and over just 2 spindles for each) cancels any negative effects of the data, logs & tempdb all being together.

]]>DavisonWed, 19 Jan 2011 06:42:59 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorTue, 18 Jan 2011 08:32:00 GMT
good article]]>bo raTue, 18 Jan 2011 08:32:00 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorWed, 12 Jan 2011 21:36:56 GMT
Franklin, the link should be fixed soon. Thanks for pointing that out.]]>KellyWed, 12 Jan 2011 21:36:56 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorTue, 11 Jan 2011 10:17:25 GMT
Andrew: Would you ask someone to put a colon after "http" in the link to listing 1? Thanks]]>FranklinTue, 11 Jan 2011 10:17:25 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorMon, 10 Jan 2011 16:50:00 GMT
This is a wonderful article on performance tuning.]]>MALIKMon, 10 Jan 2011 16:50:00 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorWed, 05 Jan 2011 09:39:40 GMT
It is wonderful to know where you actually learn about those tips. I would like to see more references in the articles.

Take Care!]]>GalvaniWed, 05 Jan 2011 09:39:40 GMThttp://www.sqlmag.com/article/project-plan-dbadmin-sql-server-2008-r2-79/sql-server/top-10-performance-tips-for-sql-server#commentsAnchorhttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorWed, 29 Dec 2010 05:12:23 GMT
Thank you!

Based on your article I checked one of my systems with a heavy "mostly Adhoc queries" application. The procedure cache uses up to 10 GB (of 128 GB) and the TokenAndPermUserStore grew up to 1.6 GB ! Your article helped us to improve performance on heavy usage periods.
]]>KastWed, 29 Dec 2010 05:12:23 GMThttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorhttp://www.sqlmag.com/article/sql-server/the-4-best-ways-to-efficiently-address-sql-server-performance-problems#commentsAnchorThu, 26 Aug 2010 17:07:52 GMT
Kenneth, Yes you are correct on the name of the DMV. Not sure how that got missed but thanks for letting us know. ]]>KellyThu, 26 Aug 2010 17:07:52 GMThttp://www.sqlmag.com/article/sql-server/the-4-best-ways-to-efficiently-address-sql-server-performance-problems#commentsAnchorhttp://www.sqlmag.com/article/sql-server/the-4-best-ways-to-efficiently-address-sql-server-performance-problems#commentsAnchorThu, 26 Aug 2010 15:05:20 GMT
Can you clarify the name of the DMF "sys.dm_db_virtual_filestatistics"? I cannot find a DMF with that name, but I do find one named "sys.dm_io_virtual_file_stats". Is that the function you are referring to in the "FILE STATISTICS" section?]]>Kenneth PowersThu, 26 Aug 2010 15:05:20 GMThttp://www.sqlmag.com/article/sql-server/the-4-best-ways-to-efficiently-address-sql-server-performance-problems#commentsAnchorhttp://www.sqlmag.com/article/sql-server/the-4-best-ways-to-efficiently-address-sql-server-performance-problems#commentsAnchorTue, 24 Aug 2010 15:09:37 GMT
Remember to replace Title Title Title Title Title Title Title with the actual title. :-)]]>SwartTue, 24 Aug 2010 15:09:37 GMThttp://www.sqlmag.com/article/sql-server/the-4-best-ways-to-efficiently-address-sql-server-performance-problems#commentsAnchorhttp://www.sqlmag.com/article/sql-server/is-tempdb-affecting-your-day-to-day-sql-server-performance-#commentsAnchorThu, 15 Jul 2010 14:26:40 GMT
Nice catch on the explicit temp table dropping issue. Good stuff!]]>GONZALEZThu, 15 Jul 2010 14:26:40 GMThttp://www.sqlmag.com/article/sql-server/is-tempdb-affecting-your-day-to-day-sql-server-performance-#commentsAnchorhttp://www.sqlmag.com/article/business-intelligence/is-your-physical-disk-i-o-affecting-sql-server-performance-#commentsAnchorMon, 17 May 2010 08:23:43 GMT
I believe that the most important is to have a baseline. To do that, every database server should have a DW of it’s metrics since day 1. This is the only way to know if the current performance is what you should expect.

Take Care!]]>GalvaniMon, 17 May 2010 08:23:43 GMThttp://www.sqlmag.com/article/business-intelligence/is-your-physical-disk-i-o-affecting-sql-server-performance-#commentsAnchorhttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorFri, 09 Apr 2010 09:35:33 GMT
Just wanted to add a link to a KB article that shows how to use a trace flag to limit the entries in the cache with SP3. http://support.microsoft.com/kb/959823
]]>KellyFri, 09 Apr 2010 09:35:33 GMThttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorhttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorFri, 26 Mar 2010 13:12:44 GMT
I just found another client with this problem. I bet this is more prevelant than most people think.]]>KellyFri, 26 Mar 2010 13:12:44 GMThttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorhttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorThu, 11 Mar 2010 12:46:02 GMT
Nice Tip!]]>MarcosThu, 11 Mar 2010 12:46:02 GMThttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorhttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorThu, 11 Mar 2010 11:55:32 GMT
Nice Tip!]]>MarcosThu, 11 Mar 2010 11:55:32 GMThttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorhttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorSat, 27 Feb 2010 15:31:22 GMT
Reading through the articles. Thanks for the info!]]>BetaSat, 27 Feb 2010 15:31:22 GMThttp://www.sqlmag.com/article/performance/do-you-have-hidden-cache-problems-#commentsAnchorhttp://www.sqlmag.com/article/performance/diving-deeper-into-wait-stats#commentsAnchorMon, 06 Jul 2009 10:53:27 GMT
Would be nice to be able to copy and paste the listings. They’ve been posted to this article as pictures.]]>CHRISMon, 06 Jul 2009 10:53:27 GMThttp://www.sqlmag.com/article/performance/diving-deeper-into-wait-stats#commentsAnchorhttp://www.sqlmag.com/article/performance/diving-deeper-into-wait-stats#commentsAnchorMon, 29 Jun 2009 08:00:12 GMT
I cannot see nor figures nor listiings]]>DamianMon, 29 Jun 2009 08:00:12 GMThttp://www.sqlmag.com/article/performance/diving-deeper-into-wait-stats#commentsAnchorhttp://www.sqlmag.com/article/performance/finding-your-top-10-sql-server-queries#commentsAnchorMon, 25 May 2009 13:44:16 GMT
Couldn’t agree more. Chase the 10 offenders and your database will get a lot better. I already do this with DMVs and I should say that it is the second most important part of my job as DBA. The first one is to secure the data.]]>MarcosMon, 25 May 2009 13:44:16 GMThttp://www.sqlmag.com/article/performance/finding-your-top-10-sql-server-queries#commentsAnchorhttp://www.sqlmag.com/article/performance/focus-on-files-and-filegroups#commentsAnchorTue, 19 May 2009 13:16:23 GMT
Good explanation about a misunderstood topic. Thanks a lot.]]>MarcosTue, 19 May 2009 13:16:23 GMThttp://www.sqlmag.com/article/performance/focus-on-files-and-filegroups#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorTue, 24 Mar 2009 10:10:20 GMT
I had to add [] around index names in the genereated ALTER INDEX and UPDATE STATISTICS to get around indexes with white spaces in the name: SET @Rebuild = N’ALTER INDEX [’ + @IndexName + N’] ON ’ + @FullName + N’ REORGANIZE’ ;]]>TorgeTue, 24 Mar 2009 10:10:20 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorTue, 24 Mar 2009 10:10:20 GMT
I had to add [] around index names in the genereated ALTER INDEX and UPDATE STATISTICS to get around indexes with white spaces in the name: SET @Rebuild = N’ALTER INDEX [’ + @IndexName + N’] ON ’ + @FullName + N’ REORGANIZE’ ;]]>TorgeTue, 24 Mar 2009 10:10:20 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorFri, 13 Feb 2009 07:00:46 GMT
The same subject again... rebuild the indexes and stuff... I know that a lot has been said about performance, I believe that everything about that has already been told, at least regarding SQL Server 2000 and 2005 under Windows 2000 and 2003. Maybe it is time to find new subjects to write about...]]>MarcosFri, 13 Feb 2009 07:00:46 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/performance/boost-performance-with-parallel-processing#commentsAnchorTue, 09 Dec 2008 18:38:32 GMT
Very interesting and informative. Thank you.]]>VenkituTue, 09 Dec 2008 18:38:32 GMThttp://www.sqlmag.com/article/performance/boost-performance-with-parallel-processing#commentsAnchorhttp://www.sqlmag.com/article/performance/finding-your-top-10-sql-server-queries#commentsAnchorThu, 13 Nov 2008 17:46:05 GMT
Yes it is amazing how much performance you can gain by addressing just a relatively few number of queries in most systems. The query_hash column in 2008 will be of great help to people running SQL 2008 and I was thinking of writing about that in a future article as well.]]>AndrewThu, 13 Nov 2008 17:46:05 GMThttp://www.sqlmag.com/article/performance/finding-your-top-10-sql-server-queries#commentsAnchorhttp://www.sqlmag.com/article/performance/finding-your-top-10-sql-server-queries#commentsAnchorTue, 04 Nov 2008 09:52:48 GMT
I like to use the DMVs to get a lot of this information. Particularly the sys.dm_exec_query_stats. And in SQL Server 2008, the view has a new column called query_hash. This column is similar to the SQL_Signature defined above, but in a hashed format so it’s useful for grouping when aggregating. I’m also a big fan of getting the biggest bang for your effort and so I totally agree with the tip on tackling the top 10 offenders in order to reduce 90 percent of the problems. I’ve also heard this as the 20-80 rule and talk about it quite a bit at http://dbwhisperer.blogspot.com/2008/08/80-20-rule-and-sql-server-performance.html and http://dbwhisperer.blogspot.com/2008/10/reporting-services-is-fun-again.html]]>MichaelTue, 04 Nov 2008 09:52:48 GMThttp://www.sqlmag.com/article/performance/finding-your-top-10-sql-server-queries#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorFri, 03 Oct 2008 15:53:49 GMT
No in this case you want to keep the index as it is actually a Primary Key constraint which is enforced by an index. It is also a clustered index which can actually help to speed isnerts over not having a clustered index at all in a lot of cases. I don’t know what it is but it sounds like it might be an IDENTITY which will not cause fragmentation when inserting and will help to keep the table defragmented in the future. Never remove PK constraints even if you see no read activity on them. The DMV mentioned in the article is only a guide and you as the DBA must make the final decisions based on your knowledge of how your database is used and maintained. I hope that helps.]]>AndrewFri, 03 Oct 2008 15:53:49 GMThttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorhttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorMon, 22 Sep 2008 14:47:45 GMT
Very good. I need just this kind of information.]]>JerryMon, 22 Sep 2008 14:47:45 GMThttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorTue, 09 Sep 2008 09:24:45 GMT
If unused index is also the high maintenance cost index then I assume it is better to drop it. I am not sure if I should remove an unsed index in our database. Here are the details... We have an audit table with a primary key. This Audit table has a clustered index on the primary key. Mostly we only insert into the audit table. We query this table only once in a while when clients requests for some history data. In last 2 months we never queried this table so this index shows up in unused index and high maintenance cost index list. SHOULD I REMOVE THIS INDEX (to improve the inserts performance) OR KEEP IT (for querying the table when clients request)?? My another concern is if I remove the only index from the table then in few days this table will be fragmented. I used the scripts from "http://msdn.microsoft.com/en-us/magazine/cc135978.aspx" to determine the high cost indexes]]>BrianTue, 09 Sep 2008 09:24:45 GMThttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorWed, 27 Aug 2008 19:20:00 GMT
It appears that you may need to uncomment out the EXEC in the sp to actually rebuild the indexes. Right now it is only printing out the commands and not actually executing them for testing purposes. So find the lines similar to the ones listed below and change the commented out statements to run the ALTER INDEX commands. -- Catch any individual errors so we can rebuild the others BEGIN TRY -- EXEC(@Rebuild); PRINT @Rebuild ;]]>AndrewWed, 27 Aug 2008 19:20:00 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/performance/sql-server-profiler-or-server-side-trace-#commentsAnchorFri, 15 Aug 2008 11:27:05 GMT
Nice article.]]>HARISHFri, 15 Aug 2008 11:27:05 GMThttp://www.sqlmag.com/article/performance/sql-server-profiler-or-server-side-trace-#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorFri, 18 Jul 2008 08:20:30 GMT
to rpanigas: please see if your database has compatlevel set to 90 :-). According to my findigs, this is the problem.]]>CalinFri, 18 Jul 2008 08:20:30 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorMon, 07 Jul 2008 12:25:24 GMT
Where is the rest of the code for this? The download only has the procedure name and the parameters, as shown in the article, not the code as advertised. This code is great I am sure, and I really want to check it out.]]>MindyMon, 07 Jul 2008 12:25:24 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorFri, 04 Jul 2008 07:53:18 GMT
Hello, Great article! I fully agree with your findings. It seems that the provided stored proc produces an error when executing (creation of the sp). Is there an updated version? I’ve executed this code on a SQL 2005 server. Below is the error Msg 102, Level 15, State 1, Procedure rebuild_indexes_by_db, Line 14 Incorrect syntax near ’. Looks like the last line is having the problem. Thanks]]>RudyFri, 04 Jul 2008 07:53:18 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorTue, 01 Jul 2008 20:57:15 GMT
DHAY1999, thanks for notifying us of the problem. The procedure in Listing 1 does indeed appear incomplete. I will contact the editor and get the problem resolved ASAP.]]>AnneTue, 01 Jul 2008 20:57:15 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorTue, 01 Jul 2008 14:37:15 GMT
Good article, but where is the rest of the procedure?]]>davidTue, 01 Jul 2008 14:37:15 GMThttp://www.sqlmag.com/article/performance/rebuild-only-the-indexes-that-need-help#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/getting-to-know-wait-stats#commentsAnchorFri, 20 Jun 2008 15:19:28 GMT
very useful article, thanks.]]>AndreaFri, 20 Jun 2008 15:19:28 GMThttp://www.sqlmag.com/article/sql-server-2005/getting-to-know-wait-stats#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/in-search-of-duplicate-indexes-on-your-tables#commentsAnchorThu, 15 May 2008 18:54:13 GMT
I just wanted to comment on one statement that I made in the original article. >>>If you have a clustered index already defined on a particular column, there’s no need for a non-clustered index on the same column.<<< While this is true for most cases as with most things in SQL Server there are exceptions to the rule. Keep in mind that the output of the report is meant to be used as a guide for potential duplicates. You must as always use good judgement and do what is proper for your enviornment.]]>AndrewThu, 15 May 2008 18:54:13 GMThttp://www.sqlmag.com/article/sql-server-2005/in-search-of-duplicate-indexes-on-your-tables#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorThu, 15 May 2008 14:42:54 GMT
I apologize for the broken links. I’ve reported the problem to our online production team. We’ll get it fixed ASAP.]]>AnneThu, 15 May 2008 14:42:54 GMThttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorThu, 15 May 2008 10:41:25 GMT
None of the links work.]]>AJThu, 15 May 2008 10:41:25 GMThttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/in-search-of-duplicate-indexes-on-your-tables#commentsAnchorFri, 02 May 2008 10:11:22 GMT
The code download link is up now (under DOWNLOAD THE CODE at the top of the article).]]>AnneFri, 02 May 2008 10:11:22 GMThttp://www.sqlmag.com/article/sql-server-2005/in-search-of-duplicate-indexes-on-your-tables#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/in-search-of-duplicate-indexes-on-your-tables#commentsAnchorFri, 02 May 2008 09:56:08 GMT
Sm8680, I’m checking into the missing-script problem and will get it resolved ASAP. Thanks for letting us know about it. Anne Grubb, Web site editor, SQL Server Magazine]]>AnneFri, 02 May 2008 09:56:08 GMThttp://www.sqlmag.com/article/sql-server-2005/in-search-of-duplicate-indexes-on-your-tables#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/in-search-of-duplicate-indexes-on-your-tables#commentsAnchorFri, 02 May 2008 08:06:10 GMT
I’m having a problem finding the code and/or the report. Please advise. Thanks]]>SteveFri, 02 May 2008 08:06:10 GMThttp://www.sqlmag.com/article/sql-server-2005/in-search-of-duplicate-indexes-on-your-tables#commentsAnchorhttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorTue, 11 Mar 2008 07:02:56 GMT
Very useful! Thanks RRS]]>RodrigoTue, 11 Mar 2008 07:02:56 GMThttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorhttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorMon, 10 Mar 2008 09:36:39 GMT
Excellent article...every DBA should have this handy to cover their...]]>RangaMon, 10 Mar 2008 09:36:39 GMThttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorhttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorFri, 29 Feb 2008 16:53:04 GMT
Yes often times the reports that come with SQL2005 can yield some good data and should be investigated. But a word of caution for the inexperienced regarding the reports. Most of these reports weere written without much regard to performance and impact on a production server. Some especially prior to SP2 can do a lot more than you expect so test them first on non-production servers. The code you mentioned is in fact similar to what the report uses as well so they make good references for where to start with your own custom queries. Storing the plans can as you stated be very useful when dealing with day to day or week to week differences in how they act. I wish we had more room in the articles to get into details like that but we can discuss them here and in the SQL Mag Performance forums as well. Thanks for pointing that out.]]>AndrewFri, 29 Feb 2008 16:53:04 GMThttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorhttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorFri, 29 Feb 2008 15:50:47 GMT
BurKaZoiD--Thanks for letting us know. I’m working to resolve the problem right now. --Christan Humphries]]>ChristanFri, 29 Feb 2008 15:50:47 GMThttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorhttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorFri, 29 Feb 2008 14:50:12 GMT
You can also run the standard report Object Execution Statistics to get essentially the same data with some pretty pie charts. This will impress you boss with no work on your part. As mentioned in the last paragraph, the problem is that on busy servers plans are more frequently recached and previous DMV info is lost. I run a version of the below query at intervals and save the results to a table. This query includes the actual query_plan. Copy the contents of query_plan column to a file with .SQLPlan extension and you have the graphical query plan history to analyze. This has been beneficial in answering the question "It was fast yesterday, why is it slow today?". -- Warning, this result set is pretty fat - pick and choose your columns select sch.name ’Schema’, object_name(s2.objectid) ’ObjectName’ , s1.*, s2.*, p.*, q.* from sys.dm_exec_query_stats s1 cross apply sys.dm_exec_sql_text(sql_handle) as s2 cross apply sys.dm_exec_query_plan(plan_handle) as q inner join sys.objects s3 on ( s2.objectid = s3.object_id ) left outer join sys.schemas sch on(s3.schema_id = sch.schema_id) left outer join sys.dm_exec_cached_plans p on p.plan_handle = s1.plan_handle where s2.dbid = db_id() order by sch.name, object_name(s2.objectid)]]>KathyFri, 29 Feb 2008 14:50:12 GMThttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorhttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorFri, 29 Feb 2008 14:10:26 GMT
Can’t download the code. I get taken to a page that says that "page" no longer exists.]]>STEVENFri, 29 Feb 2008 14:10:26 GMThttp://www.sqlmag.com/article/performance/are-your-sql-server-statements-performing-well-#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorSat, 19 Jan 2008 10:43:07 GMT
...the reports were introduced with SQL Server SP2, as far as I can remember...:)]]>Dragos-HoratiuSat, 19 Jan 2008 10:43:07 GMThttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorSat, 19 Jan 2008 10:36:32 GMT
The "Index Usage Statistics" database report lists this information too. Right click on the database name in SSMS and go to Reports ---> Standard Reports ---> Index Usage Statistics. I think that this reports is based on the sys.dm_db_index_usage_stats DMV...]]>Dragos-HoratiuSat, 19 Jan 2008 10:36:32 GMThttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorSat, 29 Dec 2007 20:16:26 GMT
I’ll check in to this. Diana May SQL Server Mag]]>DianaSat, 29 Dec 2007 20:16:26 GMThttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorFri, 28 Dec 2007 18:01:30 GMT
What happened to the links to the code?]]>ChristianFri, 28 Dec 2007 18:01:30 GMThttp://www.sqlmag.com/article/sql-server-2000/a-sql-server-2005-dmv-cleans-up-your-indexes#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/getting-to-know-wait-stats#commentsAnchorWed, 07 Nov 2007 09:42:22 GMT
There was a typo in the article and the name of the DMV was mispelled. You actually had it correct in your select statement and the actual name of the DMV is sys.dm_os_wait_stats. For future reference on DMV’s most can be found in BooksOnLine by typing sys.dm_. This will bring you to the start of the list of DMV’s and I highly recommend you browse thru them to get a feel for what is available.]]>AndrewWed, 07 Nov 2007 09:42:22 GMThttp://www.sqlmag.com/article/sql-server-2005/getting-to-know-wait-stats#commentsAnchorhttp://www.sqlmag.com/article/sql-server-2005/getting-to-know-wait-stats#commentsAnchorTue, 06 Nov 2007 14:35:06 GMT
I tried executing the following select * from sys.dm_os_wait_stats and all I get is "Invalid object name ’sys.dm_io_wait_stats’." I also did a google search on the DMV and the only hit is this article. Is there a typo in the name for this DMV?]]>KURTTue, 06 Nov 2007 14:35:06 GMThttp://www.sqlmag.com/article/sql-server-2005/getting-to-know-wait-stats#commentsAnchor