Let’s create a TRACE for Performance Analysis
USE [Test]
GO
/****** Object: Table [dbo].[Person] Script Date: 07/30/2009 15:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Person](
[ID] [int] NOT NULL,
[First] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[last] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[County] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Here is a situation – users are complaining about a slow SQL server response.
We want to quantify if that is result of a hard hitting SQL query or this is to do with some application/background jobs running on the server.
Let’s put to practise our skills from http://www.oracledbasupport.co.uk/troubleshooting-bottlenecks-using-sql-server-profiler-part-iv/ & http://www.oracledbasupport.co.uk/troubleshooting-bottlenecks-using-windows-system-monitor-part-i/
Please initiate a Trace on SQL server before going ahead. Within a SQL server Profiler window click on the top button for “Performancve Monitor” to enable logging.
SQL Server Profiler – - PART IV
Windows System Monitor
There are 5 backup types. All these backups can be carried out online. databases. Offline backups require SQLSERVER be stopped. Offline backups are useful for the master, msdb and model databases
1. Full : This takes a complete copy of the database and logs.
2. Differential : This takes a copy of all changed pages since the last full backup (for database and logs.)
3. Transaction Log : This takes a copy of the transactions logs and clears its contents on completion.
4. File or File group : This copies only the specified file or group.
5. File differential : This takes a copy of all changed pages in file since the last file backup. In situations when the backup is too big for a backup window. Read more…
Maintenance Plans are only displayed to those users who are connected to the SQL Server using Windows Authentication Mode.
Prerequisite :
Here I am going to log replicate AdventureWorks database from T-B0399254ED744 to TVLX4686
This morning I installed a new instance of SQL server and while connecting remotely it sent me an error message.
Please navigate to Management ->Activity Monitor -> Double Click
Tweet
