Timeline for Understanding partition elimination
Current License: CC BY-SA 4.0
16 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Sep 19, 2019 at 17:44 | comment | added | Justin Cave | Let us continue this discussion in chat. | |
| Sep 19, 2019 at 17:35 | comment | added | BeginnerDBA | @JustinCave- pasted in question | |
| Sep 19, 2019 at 17:29 | comment | added | Justin Cave | @BeginnerDBA - And what is the query you are running that returns 0 rows? | |
| Sep 19, 2019 at 17:27 | comment | added | BeginnerDBA | i did - select count(*) from table where $partition.pfname(trickey)=1200, it returns over 20 M rows and then 1200 part number is for the date we are searching | |
| Sep 19, 2019 at 17:12 | comment | added | Justin Cave | @BeginnerDBA - Again, that's just statistics which may or may not match what is really in the table. You'd need to run a query against the table to see how many rows are actually in the partition. You say you are doing that and getting 0 rows which implies that that partition doesn't have any data in that range. You could potentially use the $PARTITION syntax to query that specific partition learn.microsoft.com/en-us/sql/t-sql/functions/… to see what it actually contains if anything. | |
| Sep 19, 2019 at 17:00 | comment | added | BeginnerDBA | @JustinCave, i also see the same row count when checked from GUI- right click table->storage-> Manage compression and see the rows for that partition. If that's not correct then how can i check for same? | |
| Sep 19, 2019 at 16:32 | comment | added | Justin Cave | @BeginnerDBA - That script tells you the statistics on the partition. That doesn’t necessarily match the number of rows in the partition. If there is actually data in the partition between the two dates, I’d expect that you have an error in your Urey. Can you post a reproducible test case that demonstrates the issue? | |
| Sep 19, 2019 at 16:08 | comment | added | BeginnerDBA | Used the script from dbafromthecold.com/2014/06/04/partitioning-basics-part-1 to check rows in each partitions. We have total of 1373 with right boundary value for each day and as checked i can see data in that specific partition of 2019-09-13 | |
| Sep 19, 2019 at 16:03 | comment | added | BeginnerDBA | @JustinCave: i checked for the number of rows in that partition boundary range and it showed over 20 Million rows out there | |
| Sep 19, 2019 at 15:59 | comment | added | Justin Cave |
@BeginnerDBA - How do you know that there is data in the table where trickey is between 2019-09-13 and 2019-09-14 if the query returns 0 rows for that predicate? I suppose it's possible that you've encountered a tremendous bug in SQL Server that needs to be reported to Microsoft but it's much more likely that there is no data that matches the criteria you specified. Either because the data isn't what you expect or because the criteria in your query isn't correct.
|
|
| Sep 19, 2019 at 15:54 | comment | added | BeginnerDBA | @JustinCave: Yes thats correct, trickey is a datetime column [PK] and yes data is there | |
| Sep 19, 2019 at 15:44 | comment | added | Justin Cave |
@BeginnerDBA - Is trickey a datetime column? And are you sure that it has data where the value is between those two dates
|
|
| Sep 19, 2019 at 15:39 | comment | added | BeginnerDBA | @justin - i modified the code as mentioned for where clause and query returned 0 rows with execution plan showing as constant scan. So it seems changing that does not wo | |
| Sep 19, 2019 at 15:38 | comment | added | BeginnerDBA | @ErikDarling, My bad. I did not intended to do so. Later when i try to edit the option went away. I should delete it probably and check again. | |
| Sep 18, 2019 at 21:49 | comment | added | Erik Reasonable Rates Darling | Your answer is likely correct, but estimated plans don't show partition elimination. See here vs. here. | |
| Sep 18, 2019 at 20:47 | history | answered | Justin Cave | CC BY-SA 4.0 |