Partitioning | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Partitioning

Ladies and GentlemenI have an issue I am a bit dumbfounded by…I have a very large table (96,270,560 rows) which performance has been an issue. this table was never partitioned. I decided to partition it and set it up by a date field that is a varchar date (I cant have it as datetime.. it is shrink wrap software) I split it up by business quarters. I also made it so that it would not have to be split. I created many filegroups and have created the partition function and schemes (below is an example) so that I wont have to manage it much. so lots of empty files. I don’t think this would cause a performance issue. I checked the sys.partitions table and the correct partitions appear to have the right counts for each index. The test query I was using to test with went from 20 seconds to 2:40. I even updated stats.. not sure why it would slow down… tThoughts? need more info?create PARTITION FUNCTION Mytable_PFN(varchar(10))AS RANGE RIGHT FOR VALUES (‘2007-07-01′,’2007-10-01′,’2008-01-01′,’2008-04-01′,’2008-07-01′,’2008-10-01′,’2009-01-01′,’2009-04-01′,’2009-07-01′,’2009-10-01′,’2010-01-01′,’2010-04-01′,’2010-07-01′,’2010-10-01′,’2011-01-01′,’2011-04-01′,’2011-07-01′,’2011-10-01′,’2012-01-01′,’2012-04-01′,’2012-07-01′,’2012-10-01′,’2013-01-01′,’2013-04-01′,’2013-07-01′,’2013-10-01′,’2014-01-01′,’2014-04-01′,’2014-07-01′,’2014-10-01′,’2015-01-01′,’2015-04-01′,’2015-07-01′,’2015-10-01′,’2016-01-01′,’2016-04-01′,’2016-07-01′,’2016-10-01′,’2017-01-01′,’2017-04-01′,’2017-07-01′,’2017-10-01′,’2018-01-01′,’2018-04-01′,’2018-07-01′,’2018-10-01′,’2019-01-01′,’2019-04-01′,’2019-07-01′,’2019-10-01′,’2020-01-01′,’2020-04-01′,’2020-07-01′,’2020-10-01′,’2021-01-01′,’2021-04-01′,’2021-07-01′,’2021-10-01′,’2022-01-01′,’2022-04-01′,’2022-07-01′,’2022-10-01′,’2023-01-01′,’2023-04-01′,’2023-07-01′,’2023-10-01′,’2024-01-01′,’2024-04-01′,’2024-07-01′,’2024-10-01′,’2025-01-01′,’2025-04-01′,’2025-07-01′,’2025-10-01′,’2026-01-01′,’2026-04-01′,’2026-07-01′,’2026-10-01′,’2027-01-01′,’2027-04-01′,’2027-07-01′,’2027-10-01′,’2028-01-01′,’2028-04-01′,’2028-07-01′,’2028-10-01′,’2029-01-01′,’2029-04-01′,’2029-07-01′,’2029-10-01′,’2030-01-01′,’2030-04-01’)
Oh well. Solved my own issue. It had nothing to do with partitioning but had to do with indexing.
]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |