SQL Server Performance

Partitioning

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by MichaelB, Mar 24, 2010.

  1. MichaelB Member

    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')
  2. MichaelB Member

    Oh well. Solved my own issue. It had nothing to do with partitioning but had to do with indexing.

Share This Page