Reputation
4,193
Top tag
Next privilege 5,000 Rep.
Approve tag wiki edits
Badges
1 9 34
Newest
 Nice Answer
Impact
~25k people reached

1d
answered Group daily schedule into [Start date; End date] intervals with the list of week days
1d
awarded  Nice Answer
Apr
25
answered The index columns in sys.index_columns different from what are in the index
Apr
25
comment The index columns in sys.index_columns different from what are in the index
@Russ960 Is the table partitioned by ColDate? I believe the partitioning key shows up in sys.index_columns for any partition-aligned non-clustered indexes. Maybe that's your situation?
Apr
22
awarded  Enlightened
Apr
21
awarded  Good Answer
Apr
13
comment Query against sys.schemas and sys.synonyms runs very slow for one user
@MartinSmith While those settings didn't have an impact, there is something interesting going on with the cache. It seems like the cache's existence is detrimental. E.g., if I run WHILE(1=1) BEGIN DBCC FREESYSTEMCACHE ('TokenAndPermUserStore') WAITFOR DELAY '00:00:05' END in a loop forever, the query completes in under 2 minutes vs. 8 minutes typically.
Apr
13
comment Query against sys.schemas and sys.synonyms runs very slow for one user
@MartinSmith I played around with a few different settings, going as high as the max access check cache bucket count of 65536 and a access check cache quota of 262144 (recommended 1:4 ratio), but for my query it did not have any significant impact on the has_access() performance either on the first try or on subsequent queries with a "warm cache"
Apr
13
revised Query against sys.schemas and sys.synonyms runs very slow for one user
added 1 character in body
Apr
13
comment Query against sys.schemas and sys.synonyms runs very slow for one user
@MartinSmith Very interesting, I wasn't aware of the access check cache bucket count and access check cache quota configuration options previously. Will have to play around with those a bit.
Apr
13
revised Query against sys.schemas and sys.synonyms runs very slow for one user
added 908 characters in body
Apr
13
answered Query against sys.schemas and sys.synonyms runs very slow for one user
Mar
31
comment indexes and partition switching - how it works
Do you have an example script you can share? Or the code that you are using in order to determine the fragmentation %? My understanding is that fragmentation is computed on a per-partition basis. When you are switching a partition from one table to another, the data within that partition is not touched and fragmentation of that partition therefore remains unchanged by definition.
Mar
27
awarded  Nice Question
Mar
17
awarded  Nice Question
Mar
16
revised Can SQL Server system tables be defragmented?
deleted 25 characters in body
Mar
16
accepted Can SQL Server system tables be defragmented?
Mar
16
comment Can SQL Server system tables be defragmented?
I added another answer summarizing what we ended up doing, and then cleaned up the previous comments here. Thanks again for your help!
Mar
16
answered Can SQL Server system tables be defragmented?
Mar
15
comment Why should I “Avoid partitioning dimension tables” on SQL Server?
Here are just a couple of the potential downsides of partitioned tables. If you are not taking advantage of the primary benefits, such as partition switching, it might make sense to re-phrase the question as "are there any reasons that I should partition my specific dimension table in this case?". The only reason I see in the question is that you may have a lot of queries with WHERE DateEffective >= @ReportDate. Are these queries that you can't use a normal index for?