Takes as Long as it Takes SQL, Break on Me, I have a DAC…

The DAC, what is it? It is the Dedicated Administrator Console. Basically it is a way to connect to your SQL Server when all the other connections are tied up. But it takes a little bit of pre-planning so that you can use it when things go bad with your SQL Server.  Let’s enable it so you can test using it and know that it is there in the future.


EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO

I was always afraid to use the DAC because I thought I had to use the command line and I am terrible with cmd. But did you know, you can connect to it through Management Studio? Now I can feel right at home when I trouble shoot a sick server.  To connect to a server using the DAC put “admin:” in front of the name of the server like this:

DAC

I use my elevated windows login for this point, but you can use what ever sys admin account that you have.

There are a few words of caution. You can only have one connection to your DAC on each server at a time. So don’t try to connect Object explorer to it. I have SQLPrompt and noticed that my server would send an alert on Severity Error 20 because SQL Prompt was trying to connect at the same time and was failing. Just be aware that you might get an error, but if everything is correct you can look down at the connection at the bottom of the query window and see you are connected.  If someone else is using the DAC, you won’t be able to use the connection, so it is a good idea to always close it when you are done.

Kendra Little b|t has a fantastic post for Brent Ozar Unlimited about the DAC and most importantly to me, how to tell who is connected.  This is the code that she wrote to help identify who is connected:


SELECT
CASE
WHEN ses.session_id= @@SPID THEN 'It''s me! '
ELSE '' END
+ coalesce(ses.login_name,'???') as WhosGotTheDAC,
ses.session_id,
ses.login_time,
ses.status,
ses.original_login_name
from sys.endpoints as en
join sys.dm_exec_sessions ses on
en.endpoint_id=ses.endpoint_id
where en.name='Dedicated Admin Connection'

Now when SQL Server Breaks on you, there is a tool that prevents us from shattering like glass.

If Crazy = Genius, I’m rebuilding one partition at a time!

We have had an index job that has been failing for a while.  This is one of those things you really don’t want to clean up because no one is complaining, but you know you should.  I had heard that I could rebuild one partition at a time, but where to start?  Today, I worked my way through it, so here it is so that you can do it too.

First you need to find the biggest indexes, there is a good chance those are the ones that live on partitions. I am removing Primary Keys.


SELECT i.[name] AS IndexName
,SUM(ps.[used_page_count]) * 8 AS IndexSizeKB
FROM sys.dm_db_partition_stats AS ps
INNER JOIN sys.indexes AS i ON ps.[object_id] = i.[object_id]
AND ps.[index_id] = i.[index_id]
WHERE i.name NOT LIKE 'PK%'
GROUP BY i.[name]
ORDER BY IndexSizeKB DESC

The top ones are most likely the ones you want to focus on.  Next, we need to track down the partition function.


SELECT * FROM sys.partition_functions

Next we are going to figure out which partition we want to rebuild.


SELECT sprv.value AS [Value], sprv.boundary_id AS [ID]
FROM sys.partition_functions AS spf
INNER JOIN sys.partition_range_values sprv ON sprv.function_id = spf.function_id
WHERE (spf.name = N'NameOfFunctionFromPartitionFunctions')
ORDER BY [ID] ASC

Pick the number of the partition you want to rebuild.

Next use your index name from earlier.  You will also need the table name and the partition number.


ALTER INDEX [IndexName]
ON [dbo].[TableName]
REBUILD PARTITION = 3

This will rebuild just the partition that you requested.

She keeps her management studio error to minimal, Southern Style…

It is painful when you have been working on a tough query and you start to get an out of memory error.
“An error occurred while executing batch. Error message is: Exception of type ‘System.OutOfMemoryException’ was thrown”

What in the world? But it was working before! Why is this happening? Is it happening for everyone? Do I need to reboot the SQL Server?

This is a SQL Server Management Studio Error. Usually it means that you have filled up the local memory cache on your machine. Save all your queries and close Management Studio. Once you open it again you “should” be fine, but famous last words right? If that doesn’t work, just reboot. Nice clean memory and you can get back to playing with your super tough query.

Ima make a deal with the SA so the SA don’t bite no more

T-SQL Tuesday

TSQL Tuesday #63 – How do you manage security?

Yay, so this is my first official blog party post.  I was late with my post last month because I didn’t have it figured out until I saw everyone posting and wondered what was going on.

One of my goals for this year is to increase security across my 50 SQL servers.  The SA log-in is a dangerous one and I don’t want it out there on my servers, the trouble is that it is hard to exterminate. Here is my new plan.

I am going to script out my SA log-in and password using this script.

Next I am going to rename the SA log-in to something else and give it a much stronger password. This information will go into my password safe so that I have it just in case I need it.

I am going to create a dummy SA. I have many vendor databases that claim they need SA, but they really don’t and I don’t want them to have high privileges.  I am going to work on lowering the privileges to only give what they need (this will be a slow server by server process to get the permissions right).  The reason I am keeping a log in with the name “SA” is because of vendors who hard code that user name.

As I was talking to people in the #SLCSQL user group last night, someone suggested that we can also monitor that dummy SA login to watch for attacks. It is a great idea and plan to include monitoring on the new log in.

Lars Rasmussen suggested I have a user on each server that will always be there to handle running jobs and other database needs.  I plan to include this too so that when I don’t have a proper SA log in, I will still have a log in that can handle all my fun stuff.

By doing all of this, my SA won’t bite me anymore.

T-SQL Tuesday is a blog party started by Adam Machanic (b|t) just over five years ago.  This month it is hosted by Kenneth Fisher (b|t).

Biting – Bits and Bytes

A few weeks ago I found myself talking with a network engineer.  We were discussing why my data being transferred over a network was slow.  It was a small database and I didn’t understand why if we have a 1 gigabit connection, my 1 gigabyte database was slow moving over it.  I am still working this concept in my head and would love feedback of how to help me understand it better.

A bit is one binary digit of 0 or 1.  One kilo-bit (kb) is 1000 bits, One Megabit(Mb) is 1,000,000 bits and One Gigabit (Gb) is 1,000,000,000 bits. Bits are always represented as b whereas Bytes are represented as B.  Bits focus on the data being moved over the wire between two telecommunication devices and are moved as binary.  Usually the speed is measured in seconds for example 8kbps means you moved 8000 bits or 8 kilo-bits in a second between two telecommunication points. Moving 8000 binary digits in a second isn’t too bad…right?  So why is my stuff moving slow.  Isn’t that close to moving  an 8KB SQL page?  No, let’s keep going.

One Byte is equal to 8 bits.  One Kilobytes (KB) is 1024 bytes or 8192 bits. One Megabyte (MB) is 1,048,576 bytes or 8,388,608 bits and for fun, lets do Gigabytes.  One Gigabyte (GB) is 1,073,741,824 bytes or 8,589,934,592 bits.  This is a lot of math and there are wonderful calculators around to help you compute your current and desired throughput.

On to our next interesting fact: To go over the wire, your Bytes are being transferred into bits and then back up to Bytes on the other end.  So if your connection is 8kbps (remember that is kilo-bits per second) you are only moving about 1000 Bytes per second.  Let’s look at my gigabit connection. If I am moving one gigabit per second (1gbps) then I am only moving (I am rounding) about 120 Megabytes (MB) per second. That is a big difference that what I thought I should be moving.

I know there are a lot of cool ways to move data faster, but this is a concept that every DBA should keep at the back of their mind even if it is just to perform a party trick to make your network engineer friends feel included.

Happy Biting!