A Fine Slice Of SQL Server
Arun Sirpal looks at some Azure-specific DMVs, followed by the wait stats DMV:
Sys.geo_replication_links
This query will return primary and secondary databases in a geo-replication partnership. Here you will get the status of replication which could be in 1 of 3 states – pending, seeding or catch-up. You should run this under the master database.
Read on for the rest.
Comments closedArun Sirpal notices a bug with fn_dump_dblog():
Using this command creates more threads and hidden schedulers (these will only go after a restart). Depending on what version of SQL Server you are on and what Service Pack you may or may not have this issue. It was fixed in SQL 2012 SP2 onwards. So be on the cautious side when running these sorts of commands.
Also I noticed Memory bloat for the sqlservr.exe. Nothing else was running on this server, just my fn_dump_dblog script. Threads need memory too.
It’s good advice. Undocumented functions are probably more likely than documented functions to contain bugs.
Comments closedArun Sirpal shows how to use the ABORT_AFTER_WAIT attribute on an index rebuild command:
Looking into the locking you will see that ONLINE operation uses (Sch-M) on the corresponding table as part of the process (actually takes Shared Table Lock (S) at the beginning of the operation, and a Schema Modification Lock (Sch-M at the end)).
So to be granted a SCH-M lock you can’t have any conflicting locks, so what happens when / if you have a process that is updating the table and you want to use the ONLINE rebuild? Yes you will be blocked. With 2014 onwards we can control what happens if we get into this situation and for this post I am going to abort the other query causing me to wait.
Not sure I like the “Kick the other guy(s) off” part that much, but I can see uses. It’s probably more likely to go the opposite route, cancelling the rebuild if the server’s too hot.
Comments closedArun Sirpal explains when you can detach a corrupt database:
More specifically it depends on the SQL Server version. SQL 2005 and prior it was possible, with the newer versions of SQL you will not be able to issue the command.
So let’s confirm this with SQL 2016.
As Arun notes, there’s usually not much value in doing this as detaching a database doesn’t fix anything.
Comments closedArun Sirpal configures a new instance of Azure SQL Data Warehouse:
The information shown here is the DSQL (Distributed SQL) plan – When you send a SQL query to SQL Data Warehouse, the Control node processes a query and converts the code to DSQL then the Control node sends the command to run in each of the compute nodes.
The returned query plan depicts sequential SQL statements; when the query runs it may involve parallelized operations, so some of the sequential statements shown may run at the same time. More information can be found at the following URL https://msdn.microsoft.com/en-us/library/mt631615.aspx.
Arun also looks at running a simple Power BI report off of Azure SQL Data Warehouse; click through for that.
Comments closedArun Sirpal discusses ways to restore a database within Azure SQL Database:
You won’t have the ability to use the same name of the restoring database and the database that you want to replace; if you try you get the screen shot below: To get around this I think you would need to drop the old one once the new one has restored then do a rename.
This is a big difference compared to the on-prem version, so be sure to practice this before you find yourself in a crisis.
Comments closedWhat I have done here is hard-code three parameters ( database edition, start IP address and end IP address) which for my situation won’t change but I have given the ability to pass in the environment name, SQL Server name and database name.
So a prompt will be presented to the user – here you should enter the relevant details and click enter.
It’s not that difficult to do, and the scripts themselves are probably faster than fumbling around the UI.
Comments closedArun Sirpal discusses Query Performance Insight in Azure SQL Databases:
Here you will be presented with the TOP X queries based on CPU, Duration or Execution count. You will have the ability to change the time period of analysis, return 5, 10 or 20 queries using aggregations SUM, MAX or AVG.
So let’s look at what information is provided based on queries with high AVG duration over the last 6 hours.
Looks like an interesting way to get information on the few most heavily used queries.
Comments closedArun Sirpal diagnoses a slower-than-usual DBCC CHECKDB run:
All the signs of CHECKDB Latch contention.
DBCC – OBJECT – METADATA this latch can be a major bottleneck for DBCC consistency checks when indexes on computed columns exist. As a side note DBCC_Multiobject scanner is used to get the next set of pages to process during a consistency check.
Read on for the details and Arun’s solution.
Comments closedArun Sirpal shows how to log when checkpointing runs:
Via Configuration manager I enabled trace flags 3502 and 3605 – both needed to get the checkpoint information and write it to the error log.
I then shutdown the machine, on start-up I looked into the error log.
EXECXP_READERRORLOGNotice the ‘s’ in front of the spid<number>? Well that means the checkpoint was done via the automatic process; if you do a manual checkpoint it won’t see this letter.
I did not know that the “s” indicated that this was an automated process.
Comments closedCan't find what you're looking for? Try refining your search: