Chapter 14 Working with SQL Server Agent Jobs

1. Jobs : There is nothing new in the job so no explanation

2. Maintenance Plan
A Maintenance Plan can back up databases, delete old backup files from the operating system, maintain indexes, execute another job or shrink a DB

To access the Maintenance Plan Wizard, open Management Node in SSMS, right click Maintenance Plan, and choose Maintenance Plan Wizard
After that simply follow the steps and a corresponding job for the chosen task would be created

It is normally used in place of Jobs when one does not want to write TSQL code
DBA normally avoid maintenance plan due to their less flexibility, jobs are their favorite as in ING

Chapter 15 Monitoring and troubleshooting SQL Server Performance

Chapter 15 Monitoring and troubleshooting SQL Server Performance
SQL Server Profiler
1. You should specify SP: Stmtcompleted or SP:Stmtstarting event only after you
have narrowed the focus of your trace. These events capture every statement executed within a stored procedure. On high volume systems, capturing every statement can quickly generate extremely large trace logs.

2. Saving Trace Definition
The process of setting up, launching and closing traces is automated by using SQL Server Agent jobs. However, writing a trace is not easy. shortcut is to use profiler for this. After you create a new trace inside profiler that contains the events, data columns, and filters that you want, click on run and then immediately stop the trace .Under the file menu, go to the option export, script trace definition .you can use this option to generate a transact SQL batch to create a trace for either SQL 2005 or 2000. You then use this batch as basis for stored procedure that SQl server agent calls to manage the trace.

3. Saving Trace Data
If during the trace definition you specified to save to a file or table, the trace data is already saved for you .however you can explicitly write the contents of the grid inside profiler to either a file or table by accessing the file, save as, trace table options or Trace file.

4. Profiler and Performance Monitor Co-relation in SQL Server 2005
Start a trace in profiler and simultaneously start performance monitor log counter with a counter like % processor time.
Remember trace must contain start time column to be able to co-relate to perfmon
You cannot open up the co-relation with perfmon till you save a profiler trace and then load it back up.
Save trace file and stop perfmon log also
Once the profiler trace is saved, in Profiler, from the File menu, select Import Performance Data. Select the location where you stored your Performance Monitor log. Then, select from the File menu Open and then Trace. Select the location where you stored your Profiler trace. Now, you can use Performance Monitor correlation (from File/Import Performance Data)

Creating Partitions in SQ2K5

Chapter 6 Creating Partitions

Scripting already existing Partition Schemas and Partition Functions

Using management studio in the object explorer, look under /Storage - There you will find Partion Schemas and Partition Functions. From there you can script the objects.

1. To partition a table or index, you perform the following tasks:

1. Create a partition function
2. Create a partition Scheme mapped to a partition function
3. Create the table or index on the partition scheme

2. Partitioning is one SQL Server 2005 feature that does not have graphical user interface (GUI) in SSMS. You must code to perform all operations related to partitions

3. Creating a partition function (Page 210)

General Syntax

CREATE PARTITION FUNCTION partition_function_name (input_parameter_type)

AS RANGE [LEFT RIGHT]

FOR VALUES ([boundary_value…n])

Eg.

CREATE PARTITION FUNCTION partfunc (int)

AS LEFT

FOR VALUES (1000, 2000, 3000)

This Eg. Creates a partition function named partfunc that is applied to values of data type integer.

The range LEFT clause in the example specifies that each boundary point defined for the function resides in the left hand partition.

4. Creating a partition Scheme mapped to a partition function (page 215)

General Syntax

CREATE PARTITION SCHEME partition_scheme_name

AS PARTITION partition_function_name

TO (file_group_name...n)

Eg.

CREATE PARTITION SCHEME partscheme

AS PARTITION partfunc

TO ([FG1], [FG2])

5. Create the table or index on the partition scheme

Eg.

CREATE TABLE dbo.Customer.Address

(CustomerAddressID int)

on partscheme (CustomerAddressID)

6. Querying Partitions

$PARTITION function returns a partition number based on the column value for a particular partition function. It is used for

• Determine the partition number to which a particular value would respond

Select $partition.partfunc (2784) as [PartitioNum]

• Restrict a query to a specific partition

Select * from dbo.CustomerAddress where $partition.partfunc (CustomerAddressID) =3

7. Managing partitions: Split and Merge (page 226)

ALTER PARTITION FUNCTION partfunc () SPLIT RANGE (6000);

ALTER PARTITION FUNCTION partfunc () MERGE RANGE (1000);

8. SWITCH

You can use SWITCH operator of ALTER TABLE command to

• add rows to a table
• delete rows from a table

advantage is that it does not create locks as delete and insert does.

ALTER TABLE dbo.t2 SWITCH TO dbo.t1

Where dbo.t2 and dbo.t1 have same structure and indexing