A Fine Slice Of SQL Server
Lonny Niederstadt has a public service announcement:
Trace flag 2549 is no longer required for checkdb or checktable in SQL Server 2016++.
Trace flag 2562 may still benefit checkdb on a system (depends on disk characteristics and tempdb) in SQL Server 2016++, but is not expected to benefit checktable due to the already-limited scope of checktable activity.
Click through for the full story.
Leave a CommentGreg Larsen takes us through the ins and outs of synonyms in SQL Server:
Once a database object has been created, and lots of application code has been written that references the object, it becomes a nightmare to rename the object. The nightmare comes from the amount of effort and coordination work required to make the name change without the application failing. If just one place is missed when coordinating the rename, the outcome could be disastrous. This is where a synonym can help minimize the risk associated with renaming a base object.
I’ll admit that I don’t really think about synonyms much and have used them at most a couple of times in my career. I can see where they’d be useful, but that comes at the risk of something going wrong and people not even realizing they exist.
Leave a CommentSean Gallardy turns a problem on its head:
Now let’s get to the main point, which is how long the VM stays paused or stunned – remember, this is a “small” or “short” amount of time, one might even say “trivial”. When it is kept this short to where it’s “trivial” as in less than a second then all is good and you most likely won’t notice it except in very high workloads… but we should be running with VSS integration and not VM level so it’s still incorrect, but hey. When this time is not short of trivial then GOOD things start to happen, most notably that high availability kicks in.
I appreciate the framing of this post, as the failover wasn’t a problem; it merely exposes the actual problem.
Leave a Commentwhile ago, I asked in the twitterverse if there was a Postgres equivalent for the great sp_WhoIsActive script. While I didn’t get a flat-out no (which I wasn’t expecting) I didn’t get anyone pointing me in the direction of something pre-done and did get some advice that I should take it on.
Well it took quite a while and it’s still nowhere near as robust as the MSSQL version but as a first stab I have created the pg_WhoIsActive function.
While doing it I basically said that I want a 1 to 1 equivalent as much as possible but to get it out quick I had one major rule for the POC.
Check it out.
Leave a CommentAjay Dwivedi has an interesting project:
Are you tired of not knowing what is usual workload of your server? Do you want to know what is speed of your server?
If you are a developer or DBA who manages Microsoft SQL Servers, it becomes important to understand the current load vs usual load when SQL Server is slow.
Recently, for this reason, I have been working on my own automation to capture performance counters that would help me get an idea of CPU/Memory/IO/Network issues. Baselining also lets me know if the Server load has increased gradually over time.
I have created GitHub repository ‘SqlServer-Baselining-Grafana’ that contains all the scripts that will help anyone to set up the baseline on individual SQL Server instances, and then visualize the collected data using Grafana through one Inventory server with Linked Server for individual SQL Server instances.
Baselining is one of those concepts we often talk about but just as often have trouble implementing.
Leave a CommentTaiob Ali walks us through lightweight query profiling:
With the release of lightweight profiling, you can monitor real-time query progress while the query is in execution. There are a few ways to do this.
– Using System dynamic management view sys.dm_exec_query_profiles which monitors real-time query progress while the query is in execution.
– Using System dynamic management view sys.dm_exec_query_statistics_xml which returns returns query execution plan for in-flight requests.
Click through for more details, including how to enable it.
Leave a CommentGarry Bargsley continues a series on tracking SQL Agent job failures:
First step:
You must already have Database Mail configured on your SQL Server in order to follow along with these steps. Check out Steps 1 – 3 in my blog post to help you get Database Mail configured. Once you have that complete, you are ready to get started.
Read on for the rest of the steps.
Leave a CommentMoving your SQL Server instance’s TempDB files to the D: volume is recommended for performance, as long as the TempDB files fit it the D: that has been allocated, based on your VM size.
When the D: is lost due to deallocation, as expected, the subfolder you created for the TempDB files (if applicable) and the NTFS permissions granting SQL Server permission to the folder are no longer present. SQL Server will be unable to create the TempDB files in the subfolder and will not start. Even if you put the TempDB data and log files in the root of D:, after deallocation, that’s still not a solution, as the NTFS permissions to the root of D: won’t exist. In either case, SQL Server will be unable to create the TempDB files and will not start.
Read on for a few options and William’s thoughts on the relative merits of each.
Leave a CommentGuy Glantser has some tips for managing the SQL Server error log:
SQL Server maintains its own log, also called “SQL Server Error Log”. This log contains messages describing informational and error events, similar to messages that you can find in Windows logs. In fact, many of the messages found in the SQL Server Error Log can also be found in the Windows Application Log. The SQL Server Error Log is a great place to find information about what’s happening on your database server.
SQL Server uses 7 log files to store these messages. One file serves as the current log file, and every new message is written to that file. The other 6 files are archived files, and they contain previous messages. Each time SQL Server is restarted, it recycles the files. What does it mean? First, it means that a new log file is created and becomes the new current log file. Second, the oldest log file (“Archive #6”) is deleted. And third, all the other log files are pushed back one step. The previous current log file becomes “Archive #1”, the previous “Archive #1” log file becomes “Archive #2”, and so on.
Read on to see how you can change this, manage the size of log files, and retain data for a longer time.
Leave a CommentIf you want the real web version then you need to register in their website and create a team first. Yes, KaDeck is aimed at teams of people working together. You create a team online and shortly you receive a mail with team id and a secret key that you will use when running KaDeck web..
It seems that there is no host installation of KaDeck right now (rpm package, binaries or so) but only a Docker image. This forces the user to run it in Docker or a Kubernetes cluster (or one of it’s commercial distributions such as OpenShift). Another thing is that checks your license online each time you start the container. If you want to use t offline you have to do a process of offline activation. You can reach it from the administration menu.
Click through for Guy’s thoughts on the product.
Leave a Comment