Find the index fragmentation in a database

Whenever a query has a poor performance, the first step I take is to see if the indexes of the table in the query are fragmented badly.

use DB1
GO

SELECT ps.database_id, ps.OBJECT_ID, ps.index_id, si.name, ps.avg_fragmentation_in_percent,
(SELECT distinct so.name FROM sys.objects so INNER JOIN sys.indexes ON so.object_id = si.object_id) ParentTable
FROM sys.dm_db_index_physical_stats (7, NULL, NULL, NULL, NULL) AS ps
INNER JOIN sys.indexes si ON ps.OBJECT_ID = si.OBJECT_ID
AND ps.index_id = si.index_id
WHERE ps.database_id = DB_ID() AND si.name is not null AND
ps.avg_fragmentation_in_percent > 30
ORDER BY ps.avg_fragmentation_in_percent desc
GO


***Here "7" is the database Id of the curent database against which the query is run, in my e.g. DB1

you can find the database id of the current database by the following script
Use DB1
GO
SELECT DB_ID()
GO
*****************************




In the above results, I will be rebuilding the indexes of these tables.

Create non-clustered index online

USE [db1]
GO
CREATE NONCLUSTERED INDEX []
ON [dbo].[Patient_Table] ([HOSPITAL],[SSN],[Code])

GO


(online option is available ony on Entereprise edition)

Top 10 SQL Server 2008 Features for the Database Administrator (DBA)

We migrated to the new version Sql server 2008 R2 early this year but I am afraid that we have not been able to utilize all of the "awesome" features that sql 2008 comes with, though we have used SSIS, DBmail etc but that is in development aspect. Here is a list (yes of course the top 10). More blog posts to follow on the research and hands on experience on each of the follwing features.

1.Activity Monitor
2.[Sql Server Audit]
3.BackUp Compression - ( I love this feature - considering how many databases we backup daily and monthly)
4.Central Managemant Servers
5.Data Collector and Management Data Warehouse
6.Data Compression
7.Policy-Based Management ( as the team grows this will be helpful - to implement standards on that naming conventions.)
8.Predictable Performance and Concurrency
9.Resource Governor
10.Transparent Data Encryption (TDE)

ADD DROP Primary key on a SQL table

alter table DataUpdate_DTS
Add CONSTRAINT PK_ptno PRIMARY KEY (ptno)
GO


alter table DataUpdate_DTS
drop CONSTRAINT PK_DataUpdate_DTS
GO

Error : The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

Once we migrated to the SQL 2008 R2 was getting the following error

The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

I had to do the following to resolve this

The following example shows how to view the current setting of OLE Automation procedures.

Copy
EXEC sp_configure 'Ole Automation Procedures';
GO

The following example shows how to enable OLE Automation procedures.


sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO


http://msdn.microsoft.com/en-us/library/ms191188.aspx

Edit Sql 2000 DTS on sql 2005

http://www.microsoft.com/downloads/en/details.aspx?familyid=B33D2C78-1059-4CE2-B80D-2343C099BCB4&displaylang=en

Transfer SQL Server Jobs from SQL Server 2000 to 2005

Transfer SQL Server Jobs from SQL Server 2000 to 2005 / 2008
August 15, 2007 – 6:13 pm To transfer sql server jobs from sql server 2000 to sql server 2005 you can use SQL Server Business Intelligence Development Studio.

1. Click file -> new -> project to create a new Integration Services Project
2. Click view -> toolbox so you can see an overview of all the items
3. From the toolbox, drag the “transfer jobs task” to the control flow window
4. Right click the transfer job task and click edit
5. Select jobs and enter the source and destination server, you can test the connection immediately
6. Choose to transfer all jobs, or only the ones you can select from the given list
7. Set the desired IfObjectExists and EnableJobsAtDestination option and click OK
8. Right-click the transfer jobs task and click execute task

If the job fails it will turn red, if it succeeds it turns green. Click on execution results to check for errors.


A couple of reasons why the task would fail:

•the job owner does not exist on the destination server, you can modiy this on the source server in the job properties
•if you have e-mail notifications enabled on the job, be sure the operator exists on the destination server or disable this
•if the database for which the job will be executed does not exist that job transfer will fail
A couple screenshots from the transfer jobs task properties and the execution results:

taken from http://www.gregory.eu/?p=19