Posted by Jeff Rix on April 28, 2011 under DBA | 
Find Indexes to Rebuild-find indexes with a page count greater than ten thousand and fragmentation above ninety percent.
SELECT ps.database_id, c.name as table_name, ps.OBJECT_ID,
ps.index_id, b.name, ps.avg_fragmentation_in_percent, ps.page_count
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
INNER JOIN sys.all_objects as c on ps.object_id = C.object_id
AND ps.index_id = b.index_id where ps.avg_fragmentation_in_percent > 90 and ps.page_count > 10000
ORDER BY ps.avg_fragmentation_in_percent desc
Before Index is Rebuilt- fragmentation is caused by data modification and can cause leaf pages to be out of order and index pages to not be used to their maximum value.
–turn statistics on to get logical reads
SET STATISTICS IO ON
–select columns form the composite primary key (clustered index).
SELECT DISTRICT, SECTION_KEY, COURSE_SESSION, STUDENT_ID, ASMT_NUMBER FROM Student_ASMT_Grade
Fragmentation at ninety eight percent
Logical reads 141,182
Page count: 86,684
Estimated IO Cost 103.782
Estimated Operator Cost 107.59
Code to Rebuild Index
USE [YESPrep_DW]
GO
ALTER INDEX [PK_Student_ASMT_Grade] ON [dbo].[Student_ASMT_Grade] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
GO
After Index is Rebuilt- rebuilding indexes cuts logical reads in half because index is no longer fragmented. Custs Cost in half.
SET STATISTICS IO ON
SELECT DISTRICT, SECTION_KEY, COURSE_SESSION, STUDENT_ID, ASMT_NUMBER FROM Student_ASMT_Grade
Logical reads 86,998
Page count: 86,684
Estimated IO Cost 64.2128
Estimated Operator Cost 68.2128
Fragmentation is now at zero percent
Posted by Jeff Rix on April 27, 2011 under T-SQL |
1. Enter Records you want to display into a table variable
DECLARE @WALLSTREET TABLE
(STUDENT VARCHAR(60), STUDENT_ID INT, GRADE varchar(20), STATUS VARCHAR(20), COMMENT VARCHAR(500))
INSERT INTO @WALLSTREET
SELECT reg.First_Name + ‘ ‘ + reg.Last_Name as Student,
wsAssignment.Student_ID,
left(grade.GRADE_DESC, 4) as Grade,
cfgAttCode.NAME as Status,
wsComment.COMMENT
FROM
WS_ASSIGNMENT wsAssignment
LEFT OUTER JOIN ATT_WS attWS
ON wsAssignment.ID = attWS.WS_ID
LEFT OUTER JOIN CFG_ATT_CODE cfgAttCode on attWS.ATT_CODE = cfgAttCode.CODE
LEFT OUTER JOIN CSYESSQL1.YES_LIVE.DBO.REG reg on wsAssignment.STUDENT_ID = reg.Student_ID
LEFT OUTER JOIN CFG_GRADE grade on reg.Grade = grade.GRADE
LEFT OUTER JOIN WS_COMMENT wsComment
on wsAssignment.ID = wsComment.ID
WHERE
CAST(wsAssignment.START_DATE AS DATE) = CAST(@START_DATE AS DATE)
AND
(
attWS.ATT_DATE = (SELECT MAX(ATT_DATE) FROM ATT_WS WHERE WS_ID = wsAssignment.ID)
OR
attWS.ATT_DATE IS NULL
)
AND (attWS.ATT_CODE <> ‘ATT’ OR attWS.ATT_CODE IS NULL)
AND wsAssignment.BUILDING = @BUILDING_ID
update @WALLSTREET
set COMMENT = ”
where COMMENT IS NULL
2. Declare a table variable with one column to enter HTML results
DECLARE @tableHTML5 TABLE
(HTMLRECORDS VARCHAR(8000));
3. Insert the column headers into the table. Enclose select statement in single quotes. Include an opening html body table tag.
INSERT INTO @tableHTML5
SELECT ‘<html><body><table border=”1″ width = “100%”>
<TR BGCOLOR=”CornFlowerBlue”>
<td width = 25% align=”center”> STUDENT NAME
</td><td width = 18% align=”center”> STUDENT ID
</td><td width = 15% align=”center”> GRADE
</td><td width = 16% align=”center”> STATUS
</td><td width = 26% align=”center”> COMMENT
</td></tr>’;
4. Insert the results from the first table variable into the html table variable. Enclose select statement in single quotation marks. Use plus sign(+) to concatenate html with sql columns.
INSERT INTO @tableHTML5
SELECT ‘<tr>
<td width = 25% align=”center”>’+ (cast(STUDENT as varchar(60))) +
‘</td><td width = 18% align=”center”>’+ (cast(STUDENT_ID as varchar(10))) +
‘</td><td width = 15% align=”center”>’+ (cast(GRADE as varchar(20))) +
‘</td><td width = 16% align=”center”>’+ (cast(STATUS as varchar(20))) +
‘</td><td width = 26% align=”center”>’+ (cast(COMMENT as varchar(500))) +
‘</td>’
FROM @WALLSTREET
GROUP BY STUDENT, STUDENT_ID, GRADE, STATUS, COMMENT
ORDER BY STUDENT
5. Insert results into another table variable. Use Rank over to assign each row a unique rank.
DECLARE @tableHTML6 TABLE
(HTMLRECORDS VARCHAR(5000), ROWNUM INT)
INSERT INTO @tableHTML6
select HTMLRECORDS, ROW_NUMBER() OVER(ORDER BY HTMLRECORDS) AS ROWNUM from @tableHTML5
6. Set counter to loop through table while the counter is less than the max number of records. Insert the first record before the while loop. After the while loop add closing tags to the results.
DECLARE @HTMLCOUNTER INT = 1
DECLARE @MAXHTMLRECORD INT
SET @MAXHTMLRECORD = (SELECT MAX(ROWNUM) FROM @tableHTML6)
DECLARE @RESULTS NVARCHAR(MAX)
IF @MAXHTMLRECORD IS NOT NULL
BEGIN
SET @RESULTS = (SELECT HTMLRECORDS FROM @tableHTML6 WHERE ROWNUM = @HTMLCOUNTER)
WHILE @HTMLCOUNTER < @MAXHTMLRECORD
BEGIN
SET @HTMLCOUNTER = @HTMLCOUNTER + 1
SET @RESULTS = @RESULTS + (SELECT HTMLRECORDS FROM @tableHTML6 WHERE ROWNUM = @HTMLCOUNTER)
END
SET @RESULTS = @RESULTS + ‘</td></tr></table></body></html>’
END
7. Set parameters equal to the parameters used in the stored procedure or use hard coded values. Set body format to HTML. Profile name is a database mail profile. Configure using the Database Configuration Manager under the Management tab.
EXEC msdb.dbo.SP_SEND_DBMAIL
@profile_name = ‘Rise’,
@recipients = @EMAIL,
@subject = @SUBJECT,
@body = @RESULTS,
@body_format = ‘HTML’
Results should look like a table with your applied formatting
![clip_image002[4]](http://realworldsql.com/wp-content/uploads/2011/04/clip_image0024_thumb.jpg "clip_image002[4]")
![clip_image002[4]](http://realworldsql.com/wp-content/uploads/2011/04/clip_image0024.jpg "clip_image002[4]")
