Monday, July 3, 2017

CHARINDEX Performance affected by a lack of a starting position

Yes. It really is. If you have large queries that use CHARINDEX in a WHERE clause, and that SELECT is used as part of a UNION, omitting a start position will drastically affect performance. I've no idea WHY that is, but I saw it and measured performance this morning. True story.

Thursday, June 1, 2017

Killing Linked Server Connections

Have you ever tried to cancel or rollback a transaction, particularly to a linked server, more particularly to an Oracle linked server, and that "Uh oh" feeling has come over you when you've realised that it's stuck, and the only way to fix it is to restart the SQL Server service?

Fret not! Using TCPView will save you!

PLEASE NOTE: DON'T DO THIS ON A PRODUCTION SERVER. TEST IT ON A TEST SERVER. NO WARRANTY OR SUPPORT OR ANYTHING ELSE AT ALL, EVER, IS PROVIDED BY ME FOR ANYONE ELSE. IF YOU BREAK IT, YOU BOUGHT IT.

Having said that, copy TCPView to a file location accessible by the machine in question, and RDP to the machine. Open TCPView, Accept the agreement, and it will then present you with a list of all the processes currently running (it may take a while if there's a lot of connections). You'll be able to determine which connection(s) is/are the one(s) you're looking for by checking the remote address and port columns. As an example, if you have a stuck transaction to an Oracle server, the port will be 1533, and the State, Sent and Received packets columns will be blank.

Once you've found your transactions, right-click on the first one and select Close Connection. Check your favourite SQL Server monitoring tool to make sure the transaction has now disappeared. Repeat for any other stuck transactions.

Complete list of SQL Server Messages

Yes, you can query master.sys.messages to get a list of SQL Server messages, but Microsoft has provided the full list in an easy to read list.

https://technet.microsoft.com/en-us/library/cc645603(v=sql.105).aspx

Enjoy.

Wednesday, May 31, 2017

Can't remove filegroups and their associated data files?

If you've ever tested the implementation of filegroups and partitioning in SQL Server Developer or Enterprise Edition (Sorry Standard Edition), and decided you need to remove those empty files and filegroups, but couldn't, there's a reason for that. You need to drop the partition scheme, then the partition function, then the data files, then the filegroups. In that order. Specifically.

If you don't do that, you'll have 99 problems, and filegroups will be one of them.

Tuesday, May 30, 2017

T-SQL FizzBuzz

Yes... this is my own code. Yes... I also go by Stephen Falken elsewhere.

set statistics time on;

declare
 @numbers int = 10000
 ,@rangelow int = 1
 ,@rangehigh int = 100;

create table #Numbers (Number int identity(1, 1), Col1 int);

insert into #Numbers (Col1) select top (@numbers)(1) from sys.messages;

select
 case
  when (n.Number % 15 = 0) then 'FizzBuzz'
  when (n.Number % 3 = 0) then 'Fizz'
  when (n.Number % 5 = 0) then 'Buzz'  
  else cast(n.Number as varchar(6))
 end
from
 #Numbers n
where
 n.Number between @rangelow and @rangehigh;

drop table #Numbers;

Thursday, May 18, 2017

Get out of disk space jail free.

We've all had those times where we've been running something on a Dev or Test server, and even though the database is in simple mode, the log file still grows (remember to CHECKPOINT people!). It keeps growing, and growing, and then you run xp_fixeddrives and you've only got a few GB left. Uh oh, spaghetti-ohs!

In a pinch, you can add another log file, located on another drive, to allow the transaction(s) in progress to complete without hitting "Transaction Log is full due to ACTIVE_TRANSACTION". THe NKOTB Step-By-Step guide is:

  1. Find a drive with lots of space that is attached to the server.
  2. If no drive exists, attach one.
  3. Add the new file, setting the appropriate growth setting.
  4. Set the max size of the existing log file to allow enough space for the log files of other databases to have some sort of growth.
  5. Issue a CHECKPOINT to truncate the logs of data not required.
  6. Breathe.

Of course - this is a massive NO-NO on Production systems because you should have correctly sized your drives before the server was created, and have been tracking file growth and additional space requirements proactively so you never run into this issue.

Unless someone runs a terrible query in Production that fills your log files. Then you're in a pickle.

Monday, February 1, 2016

The Mystery Revealed: Why the sp_ prefix is so special and reserved...

The answer: You can run stored procedures prefixed with sp_ without exec (just like extended procedures prefixed with xp_). All the other answers are correct as well, but this is an additional one. One of those "a long time ago in a Microsoft office in Redmond" things where a developer thought "Hey, lets do this because they're special and we can just run them". That's what makes them special.

Okay, so it's probably not a big mystery, but I've never seen anyone talk about this facet of it, so I just thought I'd put it out there.

Okay, scratch all that. I just went back to test a few different scenarios, and now I don't need to supply EXEC for any stored procedures on any server versions. I'm scratching my head over this one.

Wednesday, January 27, 2016

Unaccounted Database Bloat? Check your hidden system tables.

When SQL Server Service Broker is being used in a database, any record it creates in the hidden system tables sys.sysconvgroup, sys.sysdesend and sys.sysdercv won't be included in any of the standard SQL Reports regarding Disk Space by Table (or any other report that looks at tables). Normally, this is okay, and from experience, there's usually little variance between the total data stored in tables and indexes, and the database size.

Large variances will start to appear if Service Broker Conversations aren't being completed, either due to END CONVERSATION not being called in code, or another issue. To see how many conversations are still recorded, run this query (it might take a few minutes):

SELECT
 count(1)
FROM
 sys.conversation_endpoints ep
 INNER JOIN sys.services s on ep.service_id = s.service_id
WHERE
 -- Conversations that won't be closed automatically by Service Broker...
 security_timestamp = '1900-01-01'
 -- ...and if you want to look at a particular service...
 and s.name = N'http://schemas.microsoft.com/SQL/Notifications/QueryNotificationService'

One database had well over 1,000,000 conversations that weren't closed properly. This article talks about using WITH CLEANUP to close conversations that cannot be completed normally (in this instance, the state was staying at CONVERSING).

Token-based server access validation failed

TL;DR: Check that the user has been granted "connect to SQL Server" before looking for a needle in a haystack.

The task seemed simple enough - restore a prod database to a dev server, set recovery mode, assign permissions, create data source in SQL Reporting Services, close ticket. Until this error appeared when trying to test the data source:

Date  7/08/2015 10:37:18 AM
Log  SQL Server (Current - 7/08/2015 11:37:00 AM)

Source  Logon

Message
Login failed for user 'DOMAIN\User'. Reason: Token-based server access validation failed with an
infrastructure error. Check for previous errors. [CLIENT: xxx.xxx.xxx.xxx]

Error: 18456, Severity: 14, State: 11.

Hmm. Strange - the user exists in other databases. After a little DuckDuckGo-ing, I came across this reasonably comprehensive page: Troubleshooting specific login failed error messages.

I read through it, considered the message, and checked what the author said to check. Nothing worked. I tried to open SSMS as the troubled account and connect to the SQL Server myself. No good. So going back to the instance of SSMS running under my account, I checked the permissions for the (previously working) login. What did I find? Connect to SQL didn't have a tick in the GRANT column. Put the tick in there, clicked OK, everything works.

The takeaway from this is that while the error does look like it could be related to NTLM/Kerberos/AD troubles, a slight shift in perspective when reading the description of Error Code 0x139F (from the above link) is required. For those who haven't clicked the link, the description is ""The group or resource is not in the correct state to perform the requested operation". The requested operation is to connect to the server, and the correct state would be to have the necessary permissions to connect to the server. So check the Connect permissions for the login on the server.

Monday, July 13, 2015

How to search for a value in any column (Updated)

Here's an update to a query I did a while ago to search for any value in any table.

Yeah... that's about it.

declare @tablename varchar(50)
declare @columnname varchar(50)
declare @coltablename varchar(50)
declare @searchstringvalue varchar(100)

declare @columns nvarchar(4000)
declare @sql nvarchar(4000)

declare @rows int

set @searchstringvalue = 'love Gmail' -- Put search string here
set @columns = '' -- Set a blank value because no one likes NULLs

-- This cursor gets the tables in the database that have text fields
-- Using local fast_forward makes a cursor run much faster
declare CurTables cursor local fast_forward for
select
 st.[name]
from
 sys.columns sc
 inner join sys.tables st on sc.object_id = st.object_id
where
 system_type_id in (175, 239, 99, 231, 35, 167, 241) -- string data types
 and sc.object_id in (select object_id from sys.tables where [type] = 'U') -- user tables only
 and max_length > 21
group by
 st.[name]
order by 1

open CurTables

fetch next from CurTables into @tablename

while @@fetch_status = 0
begin
 -- Build the string of columns using the table name in the cursor
 set @columns = CAST((
 select
  '[' + sc.[name] + ']' + ' LIKE ''%' + @searchstringvalue + '%'' OR '
 from
  sys.columns sc
  inner join sys.tables st on sc.object_id = st.object_id
 where
  st.[name] = @tablename
  and system_type_id in (175, 239, 99, 231, 35, 167, 241)
  and sc.object_id in (select object_id from sys.tables where [type] = 'U')
  and max_length > 21
 order by 1
 for xml path ('')) AS VARCHAR(MAX))
 
 -- Remove ' OR ' from the end of the string
 set @columns = SUBSTRING(@columns, 1, LEN(@columns) - 3)

 -- Create the query to execute with the string of columns
 set @sql = 'SELECT @numrows = COUNT(1) FROM [' + @tablename + '] WHERE ' + @columns

 -- Execute the query
 exec sp_executesql @sql, N'@numrows INT OUTPUT', @numrows = @rows OUTPUT; 
 
 -- If there's data found in a table, show the query for that table
 if @rows > 0
 begin
  --print CHAR(9) + CHAR(9) + 'Table name: ' + @tablename + ' - ' + CAST(@rows as VARCHAR(10)) + ' row(s) returned.'
  print CHAR(9) + CHAR(9) + 'SELECT * FROM ' + @tablename + ' WHERE ' + @columns
 end
 /*
 else
 begin
  print 'Table name: ' + @tablename + ' - ' + CAST(@rows as VARCHAR(10)) + ' row(s) returned.'
 end
 */

 set @columns = ''

 fetch next from CurTables into @tablename
end

close CurTables
deallocate CurTables

Wednesday, June 3, 2015

Dates, Dates, Lists of Dates

Sometimes, you just need to generate a list of dates. Sometimes, you need to get that from a table, but a lot of those sometimes, you can generate the list at runtime, and avoid as many logical reads, and as much CPU time as possible.

In this instance, there's a SQL Reporting Services report I am modifying, and the users have requested that they filter the result set by a "Week Ending XXXX-XX-XX" date. The person who originally wrote the report retrieved a list of dates where DATEPART(dw, DateColumn) = 'Sunday'. Not a bad way of doing it, but if there were no records for a particular week, then users won't get a date for that week, and miss out on knowing that there's no data for that week.

I have this strange desire to make things run as fast as possible, so I decided to re-write the query to generate the dates using a Common-Table Expression (CTE), rather than selecting from a physical table. Here's the original query and the statistics for it:

set statistics io on;
set statistics time on;

select
 distinct convert(varchar(11), DateColumn, 106) as 'WE'
 ,DateColumn
from
 TableContainingDates
where
 datename(dw, DateColumn) = 'Sunday'
order by
 DateColumn desc
 

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(73 row(s) affected)
Table 'TableContainingDates'. Scan count 9, logical reads 7390, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 658 ms,  elapsed time = 114 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
 

There's a few logical reads there, and only 658ms of CPU time. Not too bad. The report only gets run a few times a day, and obviously the data page is in the buffer cache, because there's no physical reads. So we're all good, right?

It depends on what "good" is. Here's the CTE version:

set statistics io on;
set statistics time on;

set datefirst 1;

declare @SeedDate datetime2 = dateadd(m, -3, dateadd(d, -(datepart(dw, getdate()) - 1), getdate()))

;with Dates (WEText, WeekEndingDate)
as
(
 select convert(varchar(11), @SeedDate, 106), @SeedDate
 union all
 select convert(varchar(11), dateadd(wk, 1, WeekEndingDate), 106), dateadd(wk, 1, WeekEndingDate)
 from Dates
 where WeekEndingDate < dateadd(m, 2, dateadd(d, 7 - (datepart(dw, getdate())), getdate()))
)

select convert(varchar(11), WeekEndingDate, 106), convert(varchar(10), WeekEndingDate, 120) as WeekEndingDate from Dates order by WeekEndingDate desc

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(24 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 145, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Here we've got 22% of the original scans, 1.9%% of the original logical reads, and 0 CPU time. That looks pretty good. If you're asking "Why Bother?", I don't blame you. Most people are happy with the first version, but I'm currently in a position where every single of code I write, I have to make sure it's as fast as it can possibly be, and not at all interfere with any other queries going on in the database in question. That's why.

CHARINDEX Performance affected by a lack of a starting position

Yes. It really is. If you have large queries that use CHARINDEX in a WHERE clause, and that SELECT is used as part of a UNION, omitting a st...