Index Fragmentation for SQL Server

RE: Microsoft's MCTS 70-431 certification for SQL SErver 2005 : Fragementation Section

This section was horribly boring until I figured out a way to create fragementation on a table in a database.
Here's how I did it:

-- create database called repository
create database repository
go
-- create table:
USE [repository]
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tmp]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[tmp]
(
[id]
[int] IDENTITY(1,1) NOT NULL,
[field] [varchar](64) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [repository]
)
ON [repository]
END

THEN:

1. I inserted 10,000 rows of random string data in the [field] column (I used Redgate's Data generator.. fantastic time-cutting tool)
2. I truncated some data to be smaller (random rows)
3. I added string data to be larger (random rows)
4. I deleted random rows.

What I got:

• I had fragmentation to play with (yay!).

THEN:

1. I did an alter on the ALTER index REORGANIZE. (reduced fragmentation by 1/2)
2. Then I used the alter index... rebuild. (yes, you guessed it: ALTER INDEX...REBUILD thwacks all of the fragmentation & performance is improved).

WHEN SHOULD I DEFRAG MY INDEXES?

here is a system tool to peek into what kind of fragmentation you have:

select * from sys.dm_db_index_physical_stats
-- (this system view is a "dynamic management function" = DMF. yet another acronymn to learn. yippee)

Do a INDEX....REORGANIZE if:

avg_page_space_used_in_percent is between 60 and 75
or
avg_avg_fragmentation_in_percent between 10 and 15

Do a INDEX....REBUILD if:

avg_page_space_used_in_percent is greater than 60
or
avg_fragmentation_in_percent is greater than 15

This is also a procedure I use to analyze the fragmentation (by database). I threw this together from a few sources & use it regularly:

EXEC dbo.sp_executesql @statement = N'
ALTER procedure [dbo].[database_fragmentation_check]
@database sysname
as
select object_name(dt.object_id),
si.name,
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
from ( select object_id,
index_id, avg_fragmentation_in_percent,
avg_page_space_used_in_percent
from sys.dm_db_index_physical_stats(db_id(@database),
null,
null,
null,
''detailed'')
where index_id <> 0
) as dt -- does not return information about heaps
inner join sys.indexes si on si.object_id = dt.[object_id]
and si.index_id = dt.index_id
'

Select your data as XML Format (SQL Server)

Converting the data you already have inside your database

• You can create a select statement as you would already do, with a FOR XML at the end of your select statement
  
• Options (a la carte):
  

SELECT * FROM employees FOR XML auto

FOR XML RAW	The element defaults to "row" (can further customize) <row fistcolumn ="blah" secondcolumn="blah"/>;
FOR XML AUTO	The element name is called by <employees fistcolumn="blah" secondcolumn="blah" />
FOR XML PATH	Xml elements are parsed based on column <row> <firstColumn>blah</firstColumn> <secondColumn>blah</secondColumn> <row>

FOR EXPLICIT (explained):

Here is a select statement (same as above, but with the gory details to the EXPLICIT option

SELECT 1 AS Tag, NULL AS Parent, employeeID AS [employees!1!id], employee_fname AS[employees!1!employee_fname] FROM employees FOR XML EXPLICIT, ROOT('blah')

FOR XML EXPLICIT

FOR XML EXPLICIT requires the second column to hold NULL or nonnegative integers that represent XML parent tag IDs.

Extract recipe name from MasterCook [XML] recipe format

This is a simple example of how to extract a value from a mastercook 5(?) recipe format.

The XML Section of 70-431 was monotonous, so I used my recipe program that exports to MasterCook to help me out.

---------------------CODE--------------------------

declare @int int

declare @XML VARCHAR(MAX)

set @XML = '<mx2 source="MasterCook" date="January 23, 2009"> <Summ>

<Nam> Vegetarian Sloppy Joes </Nam></Summ> <RcpE name="Vegetarian Sloppy Joes" author="">

<Serv qty="6"/> <PrpT elapsed="0:00"/> <CatS> <CatT> Main Dish </CatT> <CatT>

try </CatT> </CatS>

<IngR name="vegetable oil" unit="tablespoon" qty="1"></IngR>

<IngR name="onion" unit="medium" qty="1">

<IPrp>

chopped

</IPrp>

</IngR>

<IngR name="green pepper" qty="1">

<IPrp>

chopped

</IPrp>

</IngR>

<IngR name="cooked brown rice" unit="cups" qty="3"></IngR>

<IngR name="Mexican style pinto beans" unit="16-ounce cans" qty="2">

<INtI>

4510

</INtI>

</IngR>

<IngR name="hickory smoke barbecue sauce" unit="cup" qty="3/4">

<INtI>

3962

</INtI>

</IngR>

<IngR name="whole grain buns (4 to 6)" qty="4">

<INtI>

3667

</INtI>

</IngR>

<DirS>

<DirT>

Heat oil in large skillet over medium-high heat. Add onion and green pepper; cook 2 to 3 minutes. Add rice, beans and barbecue sauce. Simmer 10 to 15 minutes, until heated through. Serve on buns.&#013;&#010;&#013;&#010;Makes 4 to 6 servings. </DirT> </DirS> <Srce> The Rice Council </Srce> <AltS label="Internet address" source="http://www.usarice.com/domestic/recipes/"/> <Nutr> Per Serving (excluding unknown items): 341 Calories; 6g Fat (14.8% calories from fat); 13g Protein; 61g Carbohydrate; 9g Dietary Fiber; 1mg Cholesterol; 864mg Sodium. Exchanges: 3 1/2 Grain(Starch); 1/2 Lean Meat; 1/2 Vegetable; 1 Fat; 0 Other Carbohydrates.

</Nutr>

</RcpE></mx2>'

exec sp_xml_preparedocument @int OUTPUT , @XML

select * from openxml
(
@int,
'mx2/Summ/Nam',1
)

where text is not null

--------------------------------

70-431 Certification Brain Dump (Configuration Section)

Configuring Server Security principals

(Lesson 4 in Microsoft cert book)

You can use the below as flash cards: print them out and fold over the answers

What are the two authentication modes. And Which one is reccomended	Windows and Mixed Mode. Windows Mode is recommended because you can completely rely on Active Directory's integrated security model.
What two ways can you re-configure the modes of authentication	Through SMSS graphically, right-click the server properties à Security Or Type script create login ….. domain\username from WINDOWS Or Create login …. With PASSWORD = '….'
What are the three other options the cert book offers with the create login script?	Must_change – login & pw must change at login Check_expiration – SQL Server will check the expiration of the login when the user logs in Check_policy – windows will apply the local windows password policy on the SQL Server logins
Why would you use SQL Server login (server thus set to Mixed mode)	If you have a contractor working on an external project and cannot log into the network, or if they can, the bandwidth is too large. If a legacy application requires a login as such. Then you can apply the windows rules to that user login by using the check_policy method
If you have mixed mode and create a user, what is the best practice to handle that user	Add an expiration date when you create a user (create user MyUser with PASSWORD = '…' check_expiration)
What are 8 SQL Server's fixed server roles	Sysadmin Serveradmin Setupadmin Securityadmin Processadmin Dbcreator Diskadmin Bulkadmin
Describe sysadmin fixed server role	Performs any activity in SQL Server. The permissions on this role all fixed server roles
Describe serveradmin fixed server role	Configure server-wide settings
Describe setupadmin fixed server role	Adds and removes linked servers and execute some system stored procedures. (ie: sp_serveroption)
Describe the securityadmin fixed server role	Manage server logins
Describe processadmin fixed server role	Manage processes running in an instance of SQL Server
Describe dbcreator fixed server role	Creates and alters databases
Describe diskadmin fixed server role	Manages disk files
Describe bulkadmin fixed server role	Execute the BULK INSERT statement

Create a user with an expiration date, enabling the password policy

CREATE LOGIN [login name] WITH PASSWORD='password', CHECK _EXPIRATION=ON, CHECK_POLICY=ON

Modify existing login

ALTER LOGIN [login name] WITH PASSWORD = 'password'

Disable login

ALTER LOGIN [login name] DIABLE

Drop a Windows login – or user:

DROP LOGIN [domain\user] or DROP LOGIN [username]

Get login information

Select * from Sys.sql_logins

Database Mail Architecture

There are four components to the Database mail Architecture
(These will be on exam 70-431) :

• configuration components
  database mail account - contains the smtp information you have entered for the server (I use my normal email account to play around with)
  database mail profile - a generic name you choose for other applications to handle email routing. "use profile that I called 'main' "... "I want to use the 'my alternate' email profile"...

• messaging components
  this is the host database that holds all teh objects. (hint-hint: host database is is msdb).


• 
  

• executable adn logging & auditing components

enable database mail

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

---
to keep an eye on the emails going in and out, what is failing, try these (found at databasdsournal.com)

sysmail_allitems – This view allows you to return a record set that contains one row for each email message processed by Database mail.

sysmail_event_log – This view returns a row for each Windows or SQL Server error message returned when Database Mail tries to process an email message.

sysmail_faileditems – This view returns one record for each email message that has a status of failed.

sysmail_mailattachments – This view contains one row for each attachment sent

sysmail_sentitems – This view contains one record for every successfully email sent

sysmail_unsentitems – This view contains one record for every email that is currently in the queue to be sent, or is in the process of being sent.
-------
to clean up messages:
sysmail_delete_mailitems_sp – This SP permanently deletes email messages from the msdb internal Database Mail tables

sysmail_delete_log_sp - This SP deletes Database Mail log messages

Excel: Find last row in a spreadsheet

Sub xlCellTypeLastCell_Example_Row()
Dim LastRow As Long
With ActiveSheet
LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With
MsgBox LastRow
End Sub