Performance Improvements in SQL 2014(1/4)

Hi dears, it is always nice to know something new. Would like to start with a wisdom i like “Learning brains will stay younger”. It is always advised by many performance tuning expertise,

“Update Statistics before you take any other steps to tune performance”.

SQL server 2014 has new feature called incremental statistics. Really it was a nice a feature, if you are having your tables spread into many partitions. Tables on single partition won’t be benefited by this feature.  Before we know about this feature, will have a glance about Update statistics, if you did’t update statistics on the database. The query optimized will struggle to give you best execution plan, which will ruin the performance of your query for sure.

Sales Data Partitions January Partition1 February Partition2 March Partition3

Now we will see about the new feature, imagine you have a table exist over partitions. which will fill the data monthly on every partitions. For example, sales data will filled into the Sales table as given below.

Now the tables will populate the data as given above, in the middle of march you would like to make update statistics on this table.Given below is normal update statistics before SQL Server 2014.

Walk inside DMV Stacks

Hi dears, we all know well about the power of SQL Server DMVs in so many aspects. That was the reason, we have decided to walk through this series of blogs.

Today we will see about  How do I know which stored procedures are using a particular table?

I always prefer to write something about the problem i have faced and how it was resolved. Recently we have recommended our developer teams to replace a function, which generates a sequence using a scalar-value function will be used to insert into a table,which gave us big troubles while it was accessed concurrently leads to much heavy locks. So we have recommended to use another table to maintain a identity value, to use the ident_current to get the value, hence table already have another identity column.

The solution was good and acceptable, but while implementing such a thing on a existing huge database which was used over many years and by many teams. We need to know what are all the insert stored procedures will hit this table. To safely replace the new solution, we should know clearly what are all the places needs to be touched. On such a cases, the below DMV is much helpful to tell us all the related database objects linked with a specific table.

I did’t wrote much about the DMV, but just execute it by passing a table name on to referenced_entity_name, it will tell you more stories..

SELECT

referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),

referencing_object_name = o.name,

referencing_object_type_desc = o.type_desc,

referenced_schema_name,

referenced_object_name = referenced_entity_name,

referenced_object_type_desc = o1.type_desc,

referenced_server_name, referenced_database_name

--,sed.* -- Uncomment for all the columns

FROM sys.sql_expression_dependencies sed

INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id]

LEFT OUTER JOIN sys.objects o1 ON sed.referenced_id = o1.[object_id]

WHERE referenced_entity_name = 'Customers'