December 18, 2014 at 3:59 pm
I have an ongoing issue which is in critical phase at this point, any needful suggestions on this issue would be greatly appreciated.
We are running below query on SQL Server 2014 instance for X database, and this query is taking around 12 minutes to complete, where as the same query on same database is getting completed in less than 2 minutes in this case SQL instance hosted on SQL Server 2012.
PROD --> SQL Server 2012
UAT --> SQL Server 2014
To the matter of the fact UAT infrastructure and CPU & RAM assignment levels better when compared to PROD environment.
Index Rebuild, Update Stats kind of maintenance related activities are up-to-date on the database.
Below is the query which we are trying to run on both the cases.
SELECT DISTINCT OBJECT_NAME(part.object_id) as TableName
,ss.name as SchemaName
,psch.name as PartitionSchemeName
,pfun.name as PartitionFunctionName
,c.name as ColumnName
,CONVERT(NVARCHAR(128),prng.value) AS PartitionValue
,prng.boundary_id AS BoundaryId
,CASE WHEN pfun.boundary_value_on_right = 1 THEN 'R' ELSE 'L' END as RangeType
,'{0}' As ServerGroupName
FROM sys.partitions part
JOIN sys.indexes idx ON part.[object_id] = idx.[object_id] and part.index_id = idx.index_id
JOIN sys.data_spaces dsp ON idx.data_space_id = dsp.data_space_id
JOIN sys.partition_schemes psch ON dsp.data_space_id = psch.data_space_id
JOIN sys.partition_functions pfun ON psch.function_id = pfun.function_id
JOIN sys.Tables st ON st.object_id = idx.Object_id and idx.index_id < 2
JOIN sys.schemas ss ON ss.schema_id = st.schema_id
JOIN sys.index_columns ic
on(ic.partition_ordinal > 0
and ic.index_id = idx.index_id and ic.object_id = st.object_id)
JOIN sys.columns c
on(c.object_id = ic.object_id
and c.column_id = ic.column_id)
JOIN sys.partition_range_values prng ON prng.function_id=pfun.function_id
--where OBJECT_NAME(part.object_id) NOT IN ('OdsCTPView_Bss_Line_Item')
ORDER BY TableName,prng.Boundary_id
Please assist. Thanks.
December 18, 2014 at 11:35 pm
Hi Folks,
Appreciate if you have any needful inputs on my query. Thanks.
December 18, 2014 at 11:40 pm
Hi,
Please run the query on the production database server and send the Actual execution plan.
Regards,
Shafat Husain
🙂
And Your Lord Never Forgets...!! (64:19 -- Quran)
December 19, 2014 at 3:23 am
Other than that fact that there's no WHERE clause to filter the data output, I can't see anything immediately wrong with the query.
There are changes in the optimizer and major changes in the cardinality estimator in 2014. These can certainly lead to differences in behavior of queries. But, without an execution plan from each machine, I couldn't for certain suggest what the issue is here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 19, 2014 at 12:34 pm
Thanks for the replies Shafat & Grant. I have attached both the query execution plans.
We also figured out that the same query is throwing different execution plans in SQL Server 2012 and SQL Server 2014. Could be SQL Server Optimizer enhancements in 2014 causing this kind of behavior, we are not sure what is the exact cause here?
And also we have slightly tweaked the query by using DMVs instead of using system catalogs/tables, results were coming little earlier when comparing to the previous scenario. But still this is still an issue to be prone to have timeout. We are seeing risk here as well.
Any additional inputs from your end would be of real help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply