sys.dm_db_stats_properties DMV
USE DBNAME
GO
select object_id ,name,stats_id
from sys.stats where object_id = object_id ('TABLENAME')
Once you get the details, feed it to the DMF as shown below
USE DBNAME GO select OBJECT_NAME (object_id) 'Object Name' ,stats_id ,last_updated ,rows ,rows_sampled ,steps ,unfiltered_rows ,modification_counter from sys.dm_db_stats_properties(OBJECTID,STATSID)-- You can get these informations from sys.stats dmv
You will get the results and the column description is below
|
ColumnName |
Description |
| Object_ID | ID of the object. In the query above I’ve made it to return the object name |
| Stats_ID | ID of the Statistics |
| Last_Updated | Last when statistics is updated |
| Rows | No of rows in the table when statistics is updated |
| Rows_sampled | No of rows sampled during last update |
| Steps | No of steps in histogram. Histogram step information is not returned in this DMF. |
| Unfiltered_Rows | This is for filtered index. No of rows when you update the statistics for filtered index |
| Modification_Counter | Total number of modification for the column sinced last update |
USE DBNAME
GO
DBCC SHOW_STATISTICS('TABLENAME','STATSNAME’)
If you could see you will be able to get density information, range key values and all the steps for histogram. I wonder why Microsoft added this DMF? I hope you can’t use DBCC command in select statement however you can use this DMF there, this is the only place I could see its useful. They should provide some value addition with this DMF, lets say we need a column which should tell you whether update is required for the stat or else it should give you information something like average_percentage_fragmentation in index statistics so abased on that we can dynamically update the stats. Hopefully we will get some column with these details in future. So if you aren’t aware of this DMF then it’s time for you to try it.
Tags: dmf, performance, sql 2008 r2, sql 2012, statistics, stats
Trackback from your site.
Engin Ardıç
| #
Thank you for nice article! That’s so helpful for me.
VidhyaSagar
| #
Sure will plan something when you come here
Mohamed Kabiruddin
| #
Hi Vidhysagar & Team,
I am happy to hear that the SQL Server User Group in Chennai is active and going strong. I came across your blog and wanted to attend one of your user group meetings.
I am basically a Chennai-ite and work as a Microsoft BI Consultant in Australia. I am an active member of PASS SQL Team in Australia and have also presented for PASS SQL Saturdays in Australia. I visit Chennai almost every year for holidays and I would love to attend one your sessions. I am happy to speak on any particular topic of interest and discuss case studies on some of the projects and the industry in Australia as well.
I recently presented on “Agile BI with SQL Server 2012″ for PASS. I am coming down to Chennai this August 2013 for a fortnight. Please feek free to get in touch.
Cheers,
Mohamed Kabiruddin
admin
| #
@aj — Both the scripts are same. In my example I’m passing the output value to a variable. In your example you aren’t passing it instead you make the task failure or success.
ajssis
| #
You are creating a lot of extra steps that are not required in the Script task. You fail to see the line right after your code that is
Dts.TaskResult = (int)ScriptResults.Success;
The opposite of this is
Dts.TaskResult = (int)ScriptResults.Failure;
The code you should be using is
// Check if the file exists
if (File.Exists((string)(Dts.Variables["FilePath"].Value)))
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.TaskResult = (int)ScriptResults.Failure;
}
If the file exists then the Script task result will be “Success”. If the file does not exist the Script task results will be fail.
Then all you do is set the Success direction flow (the green line) to go to your data flow task. If the result is Failure then the Failure direction flow (the red line) will go to your Update error task. Be sure to RIGHT-CLICK the direction flow and select “Failure” from the pop-up menu.