May 14, 2015 at 2:04 pm
Hi,
I want to run this below query on scheduled basis and send the result to me through Sql job. It is running but not sending any result
How to set up this?
SELECT
OBJECT_NAME(A.[object_id]) as 'TableName',
B.[name] as 'IndexName',
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count] FROM
sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN
sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id
order by avg_fragmentation_in_percent, page_count desc
May 14, 2015 at 2:09 pm
You should be able to do it with dbmail, it has options to include the results of a query in either the body or as an attachment. Just set up a scheduled job to run your send dbmail call as you want 🙂
May 18, 2015 at 4:19 pm
HI,
I am able to send the email by setting up the below
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQL DBMail',
@recipients = 'ramana@yahoo.com',
@query =
'SELECT OBJECT_NAME(A.[object_id]) as TableName, B.[name] as IndexName, A.[index_id], A.[page_count], A.[index_type_desc], A.[avg_fragmentation_in_percent], A.[fragment_count]
FROM
sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,NULL) A
INNER JOIN
sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id
order by A.object_id' ,
@subject = 'Fragmentation detail in Production DB',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'Index Fragmentation Values.txt' ;
When I check the attachment the lines are not clear. It is kind of srabling.
I tried with .XLS it is also same way?
Is there any way to get more clear like line wise Index name has to come under Index name..
May 19, 2015 at 5:13 am
ramana3327 (5/14/2015)
Hi,I want to run this below query on scheduled basis and send the result to me through Sql job. It is running but not sending any result
How to set up this?
SELECT
OBJECT_NAME(A.[object_id]) as 'TableName',
B.[name] as 'IndexName',
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count] FROM
sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN
sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id
order by avg_fragmentation_in_percent, page_count desc
try this
https://www.virtualobjectives.com.au/sqlserver/saving_to_html.htm
coll and easy and step by step guide
WINNERS NEVER QUIT AND QUITTERS NEVER WIN
sqlserversdba.wordpress.com
May 19, 2015 at 7:06 pm
ramana3327 (5/14/2015)
Hi,I want to run this below query on scheduled basis and send the result to me through Sql job. It is running but not sending any result
How to set up this?
SELECT
OBJECT_NAME(A.[object_id]) as 'TableName',
B.[name] as 'IndexName',
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count] FROM
sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN
sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id
order by avg_fragmentation_in_percent, page_count desc
Instead of sending it to you, save it in a table. If it's a scheduled job, you'll know it's there. Better yet, have the nightly defrag job use it, run the stats again, and do a before'n'after comparison.
--Jeff Moden
May 19, 2015 at 9:58 pm
The purpose of this job is to comparison of fragmentation before & after the the maintenance job. I am comparing two excels manually.
Some how I don't want to create another physical table. We need to take permission...Is there any way compare this?
May 19, 2015 at 10:07 pm
ramana3327 (5/19/2015)
The purpose of this job is to comparison of fragmentation before & after the the maintenance job. I am comparing two excels manually.Some how I don't want to create another physical table. We need to take permission...Is there any way compare this?
The best method is to store it in a table.
But if you must clutter the inbox, then use a delimiter option with dbmail. That will help line the data up properly.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2015 at 4:30 am
As mentioned already save the results in a table, but add a time stamp column for when the measurement was captured, you can then compare the rows based on the date order for each index.
If you really want to stick to the email route and you want it formatted in a way that's visually better then send the results as the body of the email in html. This is another thread asking a similar question to yours and the submitter seems to be happy with the solution:
http://qa.sqlservercentral.com/Forums/Topic1686447-391-1.aspx
MCITP SQL 2005, MCSA SQL 2012
May 20, 2015 at 5:00 am
To give you a start here is one way to do it when storing in a table:
-- Create a table to store your fragmentation details
USE TEMPDB
GO
IF NOT EXISTS (SELECT TOP 1 1 FROM sys.tables WHERE name = 'Fragmentation_results')
CREATE TABLE Fragmentation_results (
ID int identity(1,1),
TableName nvarchar(256),
IndexName nvarchar(256),
index_id int,
page_count bigint,
index_type_desc nvarchar(50),
avg_fragmentation_in_percent int,
fragment_count int,
Captured datetime DEFAULT getdate()
)
Run this before and after index maintenance:
--Get the fragmentation details from your db and store in table
USE <YOUR DB NAME HERE>
GO
INSERT INTO TEMPDB..Fragmentation_results (
TableName,
IndexName,
index_id,
page_count,
index_type_desc,
avg_fragmentation_in_percent,
fragment_count
)
SELECT OBJECT_NAME(A.[object_id]) as 'TableName',
B.[name] as 'IndexName',
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count]
FROM sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A
INNER JOIN sys.indexes B
ON A.[object_id] = B.[object_id]
and A.index_id = B.index_id
order by avg_fragmentation_in_percent, page_count desc
Run this once you have captured your stats to analyse them:
--Compare the most recent fragmentation results to the previous known value
;WITH CTE_Fragmentation_Results AS
(
SELECT TableName,
IndexName,
index_id,
page_count,
index_type_desc,
avg_fragmentation_in_percent,
fragment_count,
Captured,
ROW_NUMBER() OVER(PARTITION BY TableName, IndexName, index_id ORDER BY Captured DESC) as DateOrder
from tempdb..Fragmentation_results)
SELECT MostRecent.TableName,
MostRecent.IndexName,
MostRecent.index_id,
MostRecent.page_count as new_Page_count,
Previous.page_count as old_Page_count,
MostRecent.index_type_desc,
MostRecent.avg_fragmentation_in_percent as new_avg_fragmentation_in_percent,
previous.avg_fragmentation_in_percent as old_avg_fragmentation_in_percent,
MostRecent.fragment_count as new_fragment_count,
Previous.fragment_count as old_fragment_count,
MostRecent.Captured as new_date_captured,
Previous.Captured as old_date_captured
FROM CTE_Fragmentation_Results as MostRecent
LEFT JOIN CTE_Fragmentation_Results Previous
ON MostRecent.TableName = Previous.TableName
AND MostRecent.IndexName = Previous.IndexName
AND MostRecent.index_id = Previous.index_id
AND Previous.DateOrder = MostRecent.DateOrder + 1
ORDER BY MostRecent.TableName, MostRecent.IndexName, MostRecent.index_id, MostRecent.DateOrder ASC
MCITP SQL 2005, MCSA SQL 2012
May 20, 2015 at 5:48 am
Thank you.
When I run put this query in the job, it is showing error near 'limitied' because of the single quotes. I removed the single quotes for all aliases and replaced the limited with null
SELECT
OBJECT_NAME(A.[object_id]) as 'TableName',
B.[name] as 'IndexName',
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count] FROM
sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN
sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id
order by avg_fragmentation_in_percent, page_count desc
May 20, 2015 at 11:18 am
ramana3327 (5/19/2015)
The purpose of this job is to comparison of fragmentation before & after the the maintenance job. I am comparing two excels manually.Some how I don't want to create another physical table. We need to take permission...Is there any way compare this?
You don't need permission to create a temp table and do the comparison and then send only those things that you think are out of spec. 😉
--Jeff Moden
May 20, 2015 at 1:52 pm
ramana3327 (5/20/2015)
Thank you.When I run put this query in the job, it is showing error near 'limitied' because of the single quotes. I removed the single quotes for all aliases and replaced the limited with null
SELECT
OBJECT_NAME(A.[object_id]) as 'TableName',
B.[name] as 'IndexName',
A.[index_id],
A.[page_count],
A.[index_type_desc],
A.[avg_fragmentation_in_percent],
A.[fragment_count] FROM
sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED') A INNER JOIN
sys.indexes B ON A.[object_id] = B.[object_id] and A.index_id = B.index_id
order by avg_fragmentation_in_percent, page_count desc
Luckily for you, the default value for that parameter is LIMITED. The scan mode can really impact the time it takes to run.
May 21, 2015 at 5:08 am
Jeff Moden (5/20/2015)
ramana3327 (5/19/2015)
The purpose of this job is to comparison of fragmentation before & after the the maintenance job. I am comparing two excels manually.Some how I don't want to create another physical table. We need to take permission...Is there any way compare this?
You don't need permission to create a temp table and do the comparison and then send only those things that you think are out of spec. 😉
Jeff's right. Do your query and store the results as a set of rows in a temp table. Do your updates. Run your query again and store the results in a table. Query the differences and send yourself the results. You could use a single temp table with a datetime or a run_number column on it or you could use different temp tables.
May 21, 2015 at 12:10 pm
I will try that.
Thank you all
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply