Reputation
930
Top tag
Next privilege 1,000 Rep.
See votes, expandable usercard
Badges
1 7 16
Newest
 Yearling
Impact
~216k people reached

  • 0 posts edited
  • 1 helpful flag
  • 71 votes cast
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
Nov
7
awarded  Yearling
Oct
12
comment SQL Server stuck in single_user with auto_update_statistics_async on
Finally got this resolved and opened the DB for MULTI_USER. A colleague disabled all external connections to the DB from software, which were trying to get in as well. Apparently it was just a question of odds. When enough contenders were took out, my loop for trying to set the DB back to MULTI_USER finally got through. I guess this is one of those lessons one will never forget. Thanks a lot for the help Martin!
Oct
12
comment SQL Server stuck in single_user with auto_update_statistics_async on
Thanks for the migration and edits. So far I've been unable to find anything to offer any alternatives, other than taking the instance down entirely, removing the DB files, bringing it up, deleting the DB, and creating it again from backup. The problem is, that server hosts other important DB's too so this is rather a way too drastic measure for a single DB.