GO
CREATE NONCLUSTERED INDEX [
ON [dbo].[Patient_Table] ([HOSPITAL],[SSN],[Code])
GO
(online option is available ony on Entereprise edition)
Thursday, May 26, 2011
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)
GO
CREATE NONCLUSTERED INDEX [
ON [dbo].[Patient_Table] ([HOSPITAL],[SSN],[Code])
GO
(online option is available ony on Entereprise edition)
Monday, May 23, 2011
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)
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)
Friday, May 20, 2011
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
Add CONSTRAINT PK_ptno PRIMARY KEY (ptno)
GO
alter table DataUpdate_DTS
drop CONSTRAINT PK_DataUpdate_DTS
GO
Monday, January 17, 2011
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
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
Tuesday, December 28, 2010
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:
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
Tuesday, December 7, 2010
Sql server - performance
http://www.sql-server-performance.com/articles/dba/dt_dbcc_showcontig_p2.aspx
The Results Explained
The results from the previous command will look something like the following:
DBCC SHOWCONTIG scanning 'MyTable1' table...Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16TABLE level scan performed.
-Pages Scanned................................: 18986
-Extents Scanned..............................: 2443
-Extent Switches..............................: 9238
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 25.70% [2374:9239]
- Logical Scan Fragmentation ..................: 44.58%
- Extent Scan Fragmentation ...................: 87.07%
- Avg. Bytes Free per Page.....................: 1658.7
- Avg. Page Density (full).....................: 79.51%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'MyTable2' table...Table: 'MyTable2' (183984032); index ID: 1, database ID: 16TABLE level scan performed.
- Pages Scanned................................: 28980
- Extents Scanned..............................: 3687
- Extent Switches..............................: 22565
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 16.06% [3623:22566]
- Logical Scan Fragmentation ..................: 83.05%
- Extent Scan Fragmentation ...................: 87.44%
- Avg. Bytes Free per Page.....................: 3151.1
- Avg. Page Density (full).....................: 61.07%
DBCC execution completed. If DBCC printed error messages,contact your system administrator.
In the first table, MyTable1, we see that there were 18,986 pages examined to create the report. Those pages existed within 2,443 extents, indicating that the table consumed approximately 97% (7.8 pages per extent on average) of the extents allocated for it. We then see that while examining the pages for fragmentation, the server had to switch extent locations 9, 238 times. The Scan Density restates this by indicating the percentage of all pages within all extents were contiguous. In an ideal environment, the density displayed would be close to 100. The Logical Scan Fragmentation and Extent Scan Fragmentation are indications of how well the indexes are stored within the system when a clustered index is present (and should be ignored for tables that do not have a clustered index). In both cases, a number close to 0 is preferable. There is another anomaly being displayed here that is a little difficult to explain, but it is that SQL Server allows multiple tables to exist within a single extent, which further explains the 7.8 pages per extent (multiple tables may not however exist within a page).
The next items discuss a somewhat more mundane but important issue of page utilization. Again using the first table as the example, there are an average of 1659 bytes free per page, or that each page is 79.51% utilized. The closer that number gets to 100, the faster the database is able to read in records, since more records exist on a single page. However, this must be balanced with the cost of writing to the table. Since a page split will occur if a write is required on a page that is full, the overhead can be tremendous. This is exaggerated when using RAID 5 disk subsystems, since RAID 5 has a considerably slower write time compared to its read time. To account for this, we have the ability of telling SQL Server to leave each page a certain percentage full.
DBCC REINDEX is a related tool that will reorganize your database information in much the same way Norton Defrag will work on your hard drive (see Books Online for information on how to use DBCC REINDEX). The following report displays the differences in the data after we defragmented the data using DBCC DBREINDEX.
DBCC SHOWCONTIG scanning 'MyTable1' table...Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16TABLE level scan performed.
- Pages Scanned................................: 15492
- Extents Scanned..............................: 1945
- Extent Switches..............................: 2363
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 81.94% [1937:2364]
- Logical Scan Fragmentation ..................: 15.43%
- Extent Scan Fragmentation ...................: 20.15%
- Avg. Bytes Free per Page.....................: 159.8
- Avg. Page Density (full).....................: 98.03%'
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'MyTable2' table...Table: 'MyTable2' (183984032); index ID: 1, database ID: 16TABLE level scan performed.
- Pages Scanned................................: 35270
- Extents Scanned..............................: 4415
- Extent Switches..............................: 4437
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.35% [4409:4438]
- Logical Scan Fragmentation ..................: 0.11%
- Extent Scan Fragmentation ...................: 0.66%
- Avg. Bytes Free per Page.....................: 3940.1
- Avg. Page Density (full).....................: 51.32%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Here, we can see several key improvements and some examples of how proper indexing can be very important. The most glaring items for us are how well we were able to increase the scan density. Again, using the MyTable1 table as a reference, we can see that out of 1,945 extents, there were only 2363 extent switches. Notice that the number of extent switches is now a lower number than the original number of extents. This is due to the more efficient allocation of the data. And, since there is a significant reduction of the number of extent switches, searches for large quantities of contiguous data will be fulfilled much more quickly.
The Results Explained
The results from the previous command will look something like the following:
DBCC SHOWCONTIG scanning 'MyTable1' table...Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16TABLE level scan performed.
-Pages Scanned................................: 18986
-Extents Scanned..............................: 2443
-Extent Switches..............................: 9238
- Avg. Pages per Extent........................: 7.8
- Scan Density [Best Count:Actual Count].......: 25.70% [2374:9239]
- Logical Scan Fragmentation ..................: 44.58%
- Extent Scan Fragmentation ...................: 87.07%
- Avg. Bytes Free per Page.....................: 1658.7
- Avg. Page Density (full).....................: 79.51%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'MyTable2' table...Table: 'MyTable2' (183984032); index ID: 1, database ID: 16TABLE level scan performed.
- Pages Scanned................................: 28980
- Extents Scanned..............................: 3687
- Extent Switches..............................: 22565
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 16.06% [3623:22566]
- Logical Scan Fragmentation ..................: 83.05%
- Extent Scan Fragmentation ...................: 87.44%
- Avg. Bytes Free per Page.....................: 3151.1
- Avg. Page Density (full).....................: 61.07%
DBCC execution completed. If DBCC printed error messages,contact your system administrator.
In the first table, MyTable1, we see that there were 18,986 pages examined to create the report. Those pages existed within 2,443 extents, indicating that the table consumed approximately 97% (7.8 pages per extent on average) of the extents allocated for it. We then see that while examining the pages for fragmentation, the server had to switch extent locations 9, 238 times. The Scan Density restates this by indicating the percentage of all pages within all extents were contiguous. In an ideal environment, the density displayed would be close to 100. The Logical Scan Fragmentation and Extent Scan Fragmentation are indications of how well the indexes are stored within the system when a clustered index is present (and should be ignored for tables that do not have a clustered index). In both cases, a number close to 0 is preferable. There is another anomaly being displayed here that is a little difficult to explain, but it is that SQL Server allows multiple tables to exist within a single extent, which further explains the 7.8 pages per extent (multiple tables may not however exist within a page).
The next items discuss a somewhat more mundane but important issue of page utilization. Again using the first table as the example, there are an average of 1659 bytes free per page, or that each page is 79.51% utilized. The closer that number gets to 100, the faster the database is able to read in records, since more records exist on a single page. However, this must be balanced with the cost of writing to the table. Since a page split will occur if a write is required on a page that is full, the overhead can be tremendous. This is exaggerated when using RAID 5 disk subsystems, since RAID 5 has a considerably slower write time compared to its read time. To account for this, we have the ability of telling SQL Server to leave each page a certain percentage full.
DBCC REINDEX is a related tool that will reorganize your database information in much the same way Norton Defrag will work on your hard drive (see Books Online for information on how to use DBCC REINDEX). The following report displays the differences in the data after we defragmented the data using DBCC DBREINDEX.
DBCC SHOWCONTIG scanning 'MyTable1' table...Table: 'MyTable1' (1556968673); index ID: 1, database ID: 16TABLE level scan performed.
- Pages Scanned................................: 15492
- Extents Scanned..............................: 1945
- Extent Switches..............................: 2363
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 81.94% [1937:2364]
- Logical Scan Fragmentation ..................: 15.43%
- Extent Scan Fragmentation ...................: 20.15%
- Avg. Bytes Free per Page.....................: 159.8
- Avg. Page Density (full).....................: 98.03%'
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC SHOWCONTIG scanning 'MyTable2' table...Table: 'MyTable2' (183984032); index ID: 1, database ID: 16TABLE level scan performed.
- Pages Scanned................................: 35270
- Extents Scanned..............................: 4415
- Extent Switches..............................: 4437
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.35% [4409:4438]
- Logical Scan Fragmentation ..................: 0.11%
- Extent Scan Fragmentation ...................: 0.66%
- Avg. Bytes Free per Page.....................: 3940.1
- Avg. Page Density (full).....................: 51.32%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Here, we can see several key improvements and some examples of how proper indexing can be very important. The most glaring items for us are how well we were able to increase the scan density. Again, using the MyTable1 table as a reference, we can see that out of 1,945 extents, there were only 2363 extent switches. Notice that the number of extent switches is now a lower number than the original number of extents. This is due to the more efficient allocation of the data. And, since there is a significant reduction of the number of extent switches, searches for large quantities of contiguous data will be fulfilled much more quickly.
Subscribe to:
Posts (Atom)