SQL-Ex blog

Я не могу подключиться с помощью SSMS. В настоящий момент я могла бы просто перезапустить службу SQL Server, чтобы очистить tempdb и начать сначала, тем более, что это производственная проблема, вызывающая отключение клиентов. Но дело в том, что тогда я не узнаю, что вызвало эту проблему. У меня не будет соображений относительно того, что заполнило tempdb, и как предотвратить это в будущем.

Теперь я могу сказать себе спасибо за то, что выделенное административное соединение (DAC) включено на всех моих SQ Server. Если вы не знаете, что это такое и как его включить, щелкните здесь, и Kendra Little вам объяснит. Короче говоря, вы можете подключиться с помощью DAC, даже если вы не можете подключиться из-за ошибки, подобной представленной на скриншоте (или других, когда сервер может быть слишком занят, чтобы принять подключение), но DAC имеет особый способ подключения.

Заодно вы узнаете:

Как подключиться с помощью DAC в SSMS

• Во-первых, вы должны удаленно подключиться к машине, на которой работает SQL Server.


• Для подключения с помощью DAC в SSMS вам потребуется использовать кнопку Database Engine Query, которая выделена на скриншоте ниже:
  
  
  


• В появившемся диалоговом окне авторизации вам нужно будет использовать префикс Admin:, как показано ниже:
  
  
  


• После подключения вы увидите нечто подобное следующему скриншоту в SSMS, указывающее на то, что вы подключены с помощью DAC:
  
  
  

Важное замечание. Вы можете выполнить одно подключение к DAC. Если вы попытаетесь подключиться еще раз, то получите ошибку:



Хорошая новость, однако, это все, что вам требуется сделать, чтобы попасть туда и посмотреть, что происходит.

Запросы для обнаружения главной причины заполнения tempdb

Следующая неприятность заполнения tempdb состоит в том, что вы не можете выполнять соединения. Даже к системным таблицам или представлениям. Если вы обычно пользуетесь sp_whoisactive или sp_who2, то не сможете использовать их при заполненной tempdb. Вам придется прибегнуть к запросам без соединений, чтобы добраться до причины.

sp_who

Для начала я использовала sp_who для получения списка подключений и имен баз данных, которые они использовали. Тут я заметила пользователя, создающего таблицу в tempdb:

dbname	cmd

tempdb	CREATE TABLE

Тут я предполагаю, что этот пользователь может быть злоумышленником, но я также продолжила расследовать, не было ли других пользователей, делающих что-то подозрительное. Я также просматривала каждую сессию, которая находилась в состоянии ожидания команды в tempdb.

dbname	cmd

tempdb	AWAITING COMMAND

Подсчет подключений пользователя

Я заметила с помощью sp_who, что имелось множество подключений со стороны приложения, поэтому я выполнила запрос для получения списка общего числа подключений на пользователя:

SELECT a.*

FROM

(SELECT DB_NAME(dbid) as DBName,

COUNT(dbid) as NumberOfConnections,

loginame as LoginName

FROM sys.sysprocesses

WHERE dbid > 0

GROUP BY dbid, loginame) a

ORDER BY a.DBName, a.LoginName;

Вышеприведенный запрос даст подобные результаты:



Здесь нет ничего особо тревожного, поскольку эти подключения выполнены на моей тестовой машине. Приложение подключалось 104 раза на производственной системе, что показалось мне подозрительным. Дело в том, что я не знаю обычного числа подключений, поэтому я должна продолжать анализировать конкретные подключения к tempdb.

Определение запроса, который выполняется в каждой сессии

Вернемся к результатам sp_who. Я отметила spid для всех сессий, которые «использовали» базу данных tempdb, даже если сессия находилась в спящем (sleeping) состоянии. В этом месте я выполнила следующий запрос для каждого spid:

DBCC INPUTBUFFER(63)

GO

Результаты будут походить на представленные на скриншоте, которые дают вам EventInfo. EventInfo - это запрос, который пользователь пытается выполнить.



В данном случае я создала запрос для заполнения tempdb (спасибо dbdigger за этот запрос), так что вы видите его результаты. Я знаю, что это злоумышленник. Что касается производственного случая, то, видимо, запрос с ROW_NUMBER() OVER PARTITION BY вызывал проблему. Мы предоставляем пользователям непосредственный доступ к базе данных во многих случаях (я знаю, что это не есть хорошо, и именно поэтому я это пишу), так что один бог знает, какие запросы они выполняют.

Пока я с этим разбираюсь, мне хочется точно знать, почему приложение использовало tempdb. Оказалось, что это были некоторые хранимые процедуры. Мне потребовалось посмотреть определение хранимой процедуры, которое вы не можете получить с помощью SSMS GUI, поскольку подключены через DAC. Вы не сможете выполнять навигацию в графическом интерфейсе. В этом случае я должна использовать следующий запрос для получения определения хранимой процедуры:

SELECT Name, OBJECT_DEFINITION(object_id) AS [Definition]

FROM sys.procedures

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%storedprodname%'

GO

Хранимая процедура создает временные таблицы, и это не большой сюрприз. Но она делает это постоянно, день за днем, поэтому я предположила, что был еще одноразовый пользовательский запрос, который заполнил tempdb.

Уничтожение запросов

Я двинулась дальше и убила эту пользовательскую сессию:

kill 63

Похоже, это не позволило приложению снова заработать. Это приложение, которое внешние клиенты используют для получения данных, за которые они нам заплатили, поэтому я не хотела больше сидеть и ждать. Я убила все запросы к tempdb, чтобы позволить tempdb освободиться как можно раньше.

SELECT 'KILL ' + CAST(session_id as varchar(100)) AS Sessions_to_kill

FROM sys.dm_exec_requests where session_id in (63,64,72)

GO

И все заработало.

Это был поучительный опыт. Как для пользователя, которого отрезали от производственного доступа, так и для меня, поскольку мне никогда раньше не приходилось использовать DAC. Я не понимала, что мои обычные запросы по устранению неполадок не будут работать. Еще раз спасибо за включение DAC. Я знала, что когда-нибудь мне это может понадобиться, но никогда не думала, что этот день настанет.

Ссылки по теме

1. Освоение TempDB: основы


2. 5 причин, которые следует рассмотреть при падении производительности запроса