Reputation
950
Top tag
Next privilege 1,000 Rep.
See votes, expandable usercard
Badges
1 9 16
Newest
 Taxonomist
Impact
~238k people reached

  • 0 posts edited
  • 1 helpful flag
  • 71 votes cast
Apr
11
awarded  Taxonomist
Mar
4
awarded  Notable Question
Feb
24
revised Index Seek vs Index Scan
added 9 characters in body
Dec
17
accepted Procedure permission issues with a SQL state query
Nov
7
awarded  Yearling
Oct
14
awarded  Good Answer
Oct
13
answered When is it OK to shrink a Database?
Oct
5
comment What is the effect of replacing indexes with filtered (non-null value) indexes?
Also, in some cases using page compression was far preferable, so we ended up doing that instead. It's also handy since you can simply create the existing clustered index with DROP_EXISTING = ON, to make it far, far faster than going the sparse route. Especially since it avoids the whole hassle of re-managing indexes and FK's.
Oct
5
comment What is the effect of replacing indexes with filtered (non-null value) indexes?
Also, we did change quite a few columns to sparse types. The problem with that however is that as you'll see from MSDN, altering a column type to sparse basically forces the whole clustered index to be recreated. Making this rather heavy for large, complex tables. So we renamed the constraints and the table, created a new one with the same model and original name but with sparse columns, and then transferred the data into the new table in appropriate batches. Then once checked that everything was ok and all the indexes and FK's were again in place, dropped the old tables.
Oct
5
comment What is the effect of replacing indexes with filtered (non-null value) indexes?
Hi. A bit late to the fray but yes, while we abandoned the approach described in this topic a long time ago, we did recently come back to it with a more selective approach. Basically, we looked at the statistics usage and business model to confirm the indexes on a table per table basis. Then tested it by adding a new filtered index on the side of the normal one, and checked to see over a few weeks which one ended up being used. After confirming that ONLY the filtered indexes were used in new plans, we dropped the normal nonfiltered ones.
Sep
1
awarded  Famous Question
Aug
11
asked CDC and datamodel updates, best methodology?
Aug
4
awarded  Caucus
Aug
4
awarded  Constituent
Jul
16
awarded  Popular Question
Apr
14
awarded  Popular Question
Nov
19
revised Procedure permission issues with a SQL state query
added 173 characters in body
Nov
19
comment Procedure permission issues with a SQL state query
Thanks for the suggestion. This was actually among the first alternatives I was thinking, and what I'm still considering if there's no cleaner alternative. But in this case, I'd rather avoid granting the SQLAgentOperatorRole and instead just create a scheduled job which would pass the information to a few tables every 1-5 minutes or so, allowing the DBO user to read the data from there. But it just seems unnecessarily complex just to get the results off of a few simple queries. :/
Nov
19
asked Procedure permission issues with a SQL state query
Nov
7
awarded  Popular Question