Browse by Tags

Tracking problem indexes in SQL 2000

It’s all so easy(ish) to work within SQL 2005 but the reality is that there are still more SQL 2000 databases than SQL 2005, so I’m told, and I’m supporting one of them right now. I’ve been contemplating on how to get a handle on which of my indexes are fragmenting too quickly and where the high levels...

Posted to Grumpy Old DBA (Weblog) by GrumpyOldDBA on 06-10-2008
Covering Clustered Indexes

I’ve noticed that of late I’ve become a bit more critical of a well known publication that I suspect many DBA’s read. I have subscriptions to a number of publications and for the ones that I pay for I’m generally quite content. I like paper / hard copy because I can make use of time where it’s just not...

Posted to Grumpy Old DBA (Weblog) by GrumpyOldDBA on 05-26-2008
SQL Server - Have you observed DBCC SHRINKFILE operation performance, on huge databases?

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly! Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases ETL processes, coming to the point by design...

Posted to SQL Server Knowledge Sharing Network (SqlServer-qa.net) (Weblog) by ssqa.net on 05-08-2008
TSQL to change collation of database, whats new in SQL 2008 then?

How can you change collation of a database? How do you check the existing collation setup on a database & tables? By using the below TSQL: --For database select databasepropertyex(db_name(),'collation') as collation_name go --Tables select column_name, collation_name from INFORMATION_SCHEMA.COLUMNS...

Posted to SQL Server Knowledge Sharing Network (SqlServer-qa.net) (Weblog) by ssqa.net on 03-12-2008
SQL 2008 CTP6

http://download.microsoft.com/download/3/1/5/315b8683-3765-4426-96ec-179360abb82f/Download_Instructions_ENU.htm or http://www.microsoft.com/downloads/details.aspx?FamilyId=749BD760-F404-4D45-9AC0-D7F1B3ED1053&displaylang=en Powershell provider Filtered indexes and stats!!!!!!! Data and index compression...

Posted to Jason Massie's SQL blog (Weblog) by JasonMassie on 02-20-2008
Analysing Indexes Summary

· I’ve put the work I’ve done on using the dmvs to analyse indexes onto my web site – the links to the pages are underneath. Trying to post large documents to the blog is very difficult, and probably not what a blog was intended for! · I need to do more work on the analysis of the operational stats and...

Posted to Grumpy Old DBA (Weblog) by GrumpyOldDBA on 02-18-2008
The problem with local variables

Have you ever been writing a query and just cannot get it to use the right index? This could be one of the reasons why. Let's use this query with local variables as our example. declare @Start datetime declare @End datetime select @Start = '2004-08-01 00:00:00.000' select @End = '2004-07-28 00:00:00...

Posted to Jason Massie's SQL blog (Weblog) by JasonMassie on 01-25-2008
A quick and dirty WTF???!!!11one

You can use this to get a quick and dirty picture of what is going on with the server. We basically grab a snapshot, wait a second and then get a diff. Sort as needed. select r . cpu_time , r . logical_reads , r . session_id into #temp from sys.dm_exec_requests as r waitfor delay '00:00:01' select substring...

Posted to Jason Massie's SQL blog (Weblog) by JasonMassie on 12-07-2007
Take it a step further with the index DMV's

In SQL 2000 I wanted to find unused indexes but found it to be difficult. I had to capture a huge trace that fully represents the workload and “trust” that the ITW knows what it’s talking about. You could also use the scan started trace event filtered by dbid, objectid, indexid. This procedure was very...

Posted to Jason Massie's SQL blog (Weblog) by JasonMassie on 11-25-2007
sys.dm_db_index_physical_stats

It seems my comments in my SQLBits presentation about dropping sys.dm_db_index_physical_stats into a table valued function so it can be used within queries caught the eye of an important person. Now I'd not really given much thought to what I did, I was working on the index analysis and tried to join...

Posted to Grumpy Old DBA (Weblog) by GrumpyOldDBA on 10-22-2007

Page 1 of 2 (16 items) 1 2 Next >