November 7, 2016 at 2:47 pm
Hi Team,
I'm running a simple SQL query which returns a set of columns based on a search criteria. It has to return 29 rows, but it is giving 0,10,22 rows sometimes.
-> There is no data changes
-> DBCC commands on the DB and Table returned no errors
-> Index (this table has only 1 primary index on bigint datatype column) and stats are good.
The search condition has a CAST function, which app team has included that converts the values to bigint, which is a primary key coulmn.
I tried generating execution plan, and the actual rows is showing 0 for 1 run and then for the next run it shows 29 rows.
couldn't go further, need your help.
Thanks,
Dan Brown
November 7, 2016 at 2:51 pm
Can you post the query? Execution plan might also be helpful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2016 at 3:01 pm
Thanks for the swift response. Unfortunately i cant share, as we are operating from a secure ODC.
I can say it's a select query which has condition as
where test_id in (cast(123456227 as bigint), cast(12389055 as bigint)))
November 7, 2016 at 3:12 pm
It's going to be nearly impossible to diagnose without the query, as it's probably something related to the query and/or table structure.
You can obfuscate the table and column names, both in the query and the plan (save the plan as XML and search/replace, just make sure it's still valid afterwards)
If you're willing to obfuscate, the table definition would also be useful.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 7, 2016 at 5:56 pm
dan.brown1 (11/7/2016)
Hi Team,I'm running a simple SQL query which returns a set of columns based on a search criteria. It has to return 29 rows, but it is giving 0,10,22 rows sometimes.
-> There is no data changes
-> DBCC commands on the DB and Table returned no errors
-> Index (this table has only 1 primary index on bigint datatype column) and stats are good.
The search condition has a CAST function, which app team has included that converts the values to bigint, which is a primary key coulmn.
I tried generating execution plan, and the actual rows is showing 0 for 1 run and then for the next run it shows 29 rows.
couldn't go further, need your help.
Thanks,
Dan Brown
Honestly I think it is kind of silly to ask for help with a query but not give us even a sanitized copy of anything involved. :hehe:
However, look VERY closely at the query plan (potentially the XML version of it) and examine filtering or joins that takes the number of rows from some number above 29 to below 29. There you will find your problem, or at least one of them.
BTW, did you try your query without those casts to bigint in the IN clause?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 7, 2016 at 6:30 pm
Query hints? NOLOCK?
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
November 7, 2016 at 8:14 pm
I know, it's a kind of silly thing to ask for help without giving more info. We are unfortunate, that our secure ODC blocks all out going traffic, and access to internet and mobiles.
However, thanks for your inputs.
Yes,i tried running the query without CAST in the IN clause and it was returning same no.of rows (29) all the times. But, i'm not quite sure why the CAST is causing the issue.
But, this is not a query that was just asked for fun. We are facing this issue in our live environment.
November 7, 2016 at 8:42 pm
dan.brown1 (11/7/2016)
I know, it's a kind of silly thing to ask for help without giving more info. We are unfortunate, that our secure ODC blocks all out going traffic, and access to internet and mobiles.However, thanks for your inputs.
Yes,i tried running the query without CAST in the IN clause and it was returning same no.of rows (29) all the times. But, i'm not quite sure why the CAST is causing the issue.
But, this is not a query that was just asked for fun. We are facing this issue in our live environment.
I don't ask questions for fun either when helping someone solve a bad data issue either. 😉
So you have a workaround at least. If you can provide just the OPERATIONS of the query (such as TOP, ORDER BY, etc) or if there are UDFs of any kind in play, or other things in the WHERE clause that would be most helpful. SET statements. The more I think the longer the list gets.
IIRC SQL Sentry's Plan Explorer Pro has an ability to anonymize query plans so you could post it.
My best suggestion is to hire a professional tuner that you can give access to the system and let them tell you exactly what the issue is - likely within minutes.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 8, 2016 at 12:37 am
Does the query use DISTINCT?
😎
November 8, 2016 at 12:44 am
no it doesn't have DISTINCT
November 8, 2016 at 12:57 am
Formatted for easier readability:
Edit: why removed?
Can you try a few things?
The same query with the CASTs removed. (you said that works fine)
The same query with the constants replaced with BIGINT variables assigned to the same values (and no CAST)
The same query with the constants replaced with INT variables assigned to the same values (with the CAST)
Trying to see what breaks and what doesn't
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 9, 2016 at 10:56 am
Are you querying everything from one table or doing some type of join(s)?
----------------------------------------------------
November 10, 2016 at 3:28 pm
Hi,
I was wondering, where exactly do you see that the results are returning randomly?
From experience, I have seen MS SQL Query Analyzer ( I prefer it over Mngt Studio to write queries ) return different result sets with the same query. I believe this is because the table has a field with an image datatype, and for some reason QA doesn't process the results that well.
It could also be within the application ( I assume that there is one) that processes the result set. Is it filtering the result set somehow that you aren't noticing? Maybe the application layer is modifying the search criteria un-expectantly?
Can you perform a SQL Server Profile trace on the database to ensure that the query you expect to have been run, is exactly what is being run on the server? Can you copy and paste that query into Mngt Studio and get the same results?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply