Override

Andy Mallon teaches us when we need to use WITH OVERRIDE on RECONFIGURE:

Should I use WITH OVERRIDE?

Probably not.

That was an easy post. All done! Thanks for reading.

 

……Oh, right. Let’s talk about why.  Books Online actually has the answer:

If that’s too too heavy of reading, you can spend the day on the Transformers Wiki.  I mean, it is Friday, after all.

Starting And Stopping Local Instances

Slava Murygin shows ways to simplify starting and stopping SQL Server services on local instances:

Step 3: Easily Stop SQL Server Service.

That is very possible you performed some very heavy and memory intense operation by your local SQL Server instance and it ate all your free memory. You do not need it anymore, but SQL Server won’t easily give memory back.
The easiest way to claim all your memory is to stop your SQL Server.
There are some easy ways doing it:
– First way us using SQL Server Management Studio. You just have to do a right click on your local SQL Server instance and choose “Stop”.

The use case for Slava’s advice is a scenario in which you have SQL Server installed on a local machine with very little RAM.

Finding Objects Using T-SQL

Derik Hammer shares a couple of snippets he uses to find objects and SQL Agent jobs.

Here’s one of my favorites, which searches for code within stored procedures, functions, and views:

SELECT
OBJECT_SCHEMA_NAME(sm.object_id) AS SchemaName,
OBJECT_NAME(sm.object_id) AS ObjectName,
CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id)) AS FullName,
CONCAT(OBJECT_SCHEMA_NAME(sm.object_id), '.', OBJECT_NAME(sm.object_id), ',') AS CommaSeparatedName,
definition
FROM sys.sql_modules sm
WHERE
sm.definition LIKE '%DEFINITION%'
--AND OBJECT_SCHEMA_NAME(sm.object_id) = 'Something'
--AND OBJECT_NAME(sm.object_id) = 'Something'
ORDER BY
SchemaName,
ObjectName;

When CHECKDB Fails

Andy Galbraith has a tale of woe and a cautionary message:

Paul’s blog post “Issues around DBCC CHECKDB and the use of hidden database snapshots” discusses the need to have certain permissions to be able to create the snapshot CHECKDB uses.  I checked the DATA directory and the SQL Server default path and found that the service account did have Full Control to those locations.

What happened next ultimately resolved my issue, and it reflects something I constantly tell people when they ask me how I research things relatively quickly (most of the time anyway :)) – whenever you read a blog post or article about a subject, MAKE SURE TO READ THE FOLLOW-UP COMMENTS!  Sometimes they are nothing beyond “Great Article!” but quite often there are questions and answers between readers and the author that add important extra information to the topic, or just “Don’t Forget This!” style comments that add more detail.

Indeed.

Checking Statistics Validity

Bob Dorr has scripts to tell if your statistics are accurate:

The dilemma we all run into is what level of SAMPLED statistics is appropriate?   The answer is you have to test but that is not always feasible and in the case of Microsoft CSS we generally don’t have histogram, historical states to revisit.

Microsoft CSS is engaged to help track down the source of a poorly performing query.   It is common step to locate possible cardinality mismatches and study them closer.   Studying the statistics dates, row modification counter(s), atypical parameters usage and the like are among the fundamental troubleshooting steps.

Good post and great scripts, even if he Microsoftly nouns the verb “ask.”

Always Encrypted

Kenneth Nielsen takes a look at Always Encrypted:

The way Microsoft have implemented this always encrypted feature, is to let all the data in the tables be encrypted. The application that needs to look at data will have to use the new Enhanced ADO.net library, which will give your application the methods to de/encrypt data.

This way, the only way to insert data into a table, which contains encrypted columns, is to use parameterized insert statements from your application. It is not even possible to insert data from SQL Server Management Studio, if we try, the statement will fail.

This way we ensure that only the persons using the application will be looking at un-encrypted data, thus reducing the number of people with a direct access to sensitive data.

If you go down this route, it looks like the only method available for modifying data is going through ADO.NET, although that could change later.  My biggest concern here is how much of a performance hit—if any—systems will take.

DBA Morning Checklist

Pieter Vanhove has published his Policy-Based Management-based DBA Morning Checklist and has some post-Summit additions:

Optimize for Ad Hoc Workloads

The policy is going to check if the server setting Optimize for Ad Hoc Workloads, is set to True. By default, this setting is set to False.
The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. More information can be found on https://msdn.microsoft.com/en-us/library/cc645587.aspx

I don’t see any downside by enabling this setting.

Not many shops use PBM, so I’m happy to see Pieter contributing this to the general community.

SQL Server 2016 IFI

Nic Cain has an outstanding blog post on enabling Instant File Initialization in SQL Server 2016, specifically wondering what happens when group policy explicitly prohibits setting Perform Volume Maintenance Tasks privileges:

Much to my surprise the virtual SQL account showed up in the PVMT secpol setting. I had no idea how it got there. Reviewing the setting I was able to confirm that the account I used for install was not able to make any adjustments and yet somehow the permissions were set.

I’m happy to hear why I’m wrong, but I’d consider this a reasonable instance of privilege escalation:  I may not want the DBA to be able to perform volume maintenance tasks on just any server, but I do want him to do it on SQL Server instances.

Why Virtualize?

David Klee answers why you might still want to virtualize a single-instance SQL Server which resides on a single host:

That’s a wonderful question, and I get asked this all the time.

I can justify the desire for virtualization in the scenario you described. There are a number of reasons to consider virtualization given those constraints.

Virtualize everything, as Klee suggests.  The worst case is that administration gets slightly more complex, but the advantages are worth it.

Session Wait Stats

SQL Server 2016 has a per-session wait stats DMV:  sys.dm_exec_session_wait_stats.  That’s exciting; wait stats are extremely interesting, but until now, impossible to use on a per-item level in a busy production system (where you’d most want to use them).

Daniel Farina looks at how the new DMV relates to sys.dm_exec_wait_stats (via Database Weekly):

[R]esetting the data of sys.dm_os_wait_stats operating system view doesn’t affect the values of sys.dm_exec_session_wait_stats view.

Based on my MSDN reading, the sys.dm_exec_session_wait_stats DMV resets if the connection pool context is re-used or if the session closes.  This is why DBCC SQLPERF doesn’t include a reset option for session-specific wait stats.

Categories

January 2019
MTWTFSS
« Dec  
 123456
78910111213
14151617181920
21222324252627
28293031