Archive for the ‘SQL Server’ Category

Microsoft SQL Server 2008/2012 – SQL agent DCOM errors Event ID 10016

Mittwoch, März 19th, 2014

mswindows_logo.jpg   Here is the work around to resolve this error

Click Start -> Run -> dcomcnfg
Component services, computers, my computer,
DCOM config, find the MSDTSServer100
Properties
Security
Launch and Activate permissions – edit
Add the local SQL AgentUser group and enable local launch and Local activation

MicrosoftSQL_01

Microsoft SQL Server – sample script illustrates how to check index fragmentation of a specified database

Samstag, Oktober 19th, 2013

mswindows_logo.jpg   This script can be used for checking index fragmentation percentage of index tables in SQL Server databases

SELECT b.name, ROUND(ps.avg_fragmentation_in_percent,0)
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID AND ps.index_id = b.index_id
WHERE ps.database_id = DB_ID() AND ps.avg_fragmentation_in_percent > 10 AND ps.index_id > 0 AND ps.page_count > 10 * 1024 / 8
ORDER BY b.name

MS_SQL_Index_00

Microsoft SQL Server 2008 R2 – creating a System Maintenance Plan

Dienstag, Oktober 1st, 2013

mswindows_logo.jpg   Microsoft SQL Server 2008 R2 System Maintenance Plan

MaintenancePlan_01

Microsoft SQL Server – create stored procedure ‚DefragmentIndexes‘ in master database

Montag, September 30th, 2013

mswindows_logo.jpg   When a query is executed on a database, indexes are used to physically locate the data on disk. Due to frequent insert, update and delete statements this data can become fragmented. This is a serious performance issue. To mitigate this schedeule this script in a SQL Agent Job to frequently defragment you indexes. Create stored procedure DefragmentIndexes in master database. This stored procedure defragments the indexes in your database

SQLServerIndexFragScript_01

PRTG SQL Server Monitoring – identify issues before they become problems

Samstag, September 28th, 2013

prtg_logo.jpg   PRTG SQL Server Monitoring a powerful tool to monitor SQL servers. SQL servers are widely used for data storage and lots of applications and their performance depend on their constant availability and performance

PRTG_SQLMonitor_01

Microsoft SQL Server – improve SQL Server performance with a simple query

Montag, September 16th, 2013

mswindows_logo.jpg   Run this query on a Microsoft SQL Server 2005 or later and it will list indexes that you could create to improve the query performance of your database based on the workload of the server since the SQL service last restarted

SELECT    ‚CREATE INDEX IX_Auto_‘ +
CONVERT(varchar(max), MID.index_handle) +
‚ ON ‚ +
[statement] +
‚ (‚ +
COALESCE(equality_columns + ‚, ‚ + inequality_columns, equality_columns, inequality_columns) +
‚)‘ +
ISNULL(‚ INCLUDE (‚ + included_columns + ‚)‘, “) AS create_statement,
CONVERT(int, avg_total_user_cost * user_seeks * avg_user_impact) AS potential_saving,
[statement] AS table_name,
equality_columns,
inequality_columns,
included_columns,
last_user_seek,
avg_total_user_cost,
user_seeks,
avg_user_impact
FROM      sys.dm_db_missing_index_details MID
JOIN      sys.dm_db_missing_index_groups MIG ON MIG.index_handle = MID.index_handle
JOIN      sys.dm_db_missing_index_group_stats MIGS on MIGS.group_handle = MIG.index_group_handle
ORDER BY  avg_total_user_cost * user_seeks * avg_user_impact DESC