sobota, 6 listopada 2010

tabela tymczasowa i jej spid

Jak znaleźć właściciela tabeli tymczasowej a w zasadzie jego sesion id (SPID), dziwne ale nie ma takiej bezpośredniej metody. Można skojarzyć trace robione z serwera z obiektami, ale co jeżeli nie monitorujemy zdarzeń. Można pobawić się w detektywa

Robimy tabelkę tymczasową na sesji 65

CREATE TABLE #rk_test (i VARCHAR(100))
go

INSERT #rk_test
SELECT 'tekst'
go 1000

z innej sesji szukamy tabeli w bazie tempdb

select id ,NAME, DB_ID() AS bazaid
from  tempdb.sys.sysobjects
where xtype ='U'
and name like '#rk_test%'

id          NAME               bazaid
---------------------------------------------- ------
474333853   #rk_test____________________________________________________________________________________________________________000000001D2C 1


Sprawdzamy ile stron zajmuje na dysku


     CREATE TABLE #dbcc_ind(PageFID         smallint,
                            PagePID         int,
                            IAMFID          int,
                            IAMPID          int,
                            ObjectID        int,
                            IndexID         int,
                            PartitionNumber bigint,
                            PartitionID     bigint,
                            Iam_Chain_Type  varchar(80),
                            PageType        int,
                            IndexLevel      int,
                            NexPageFID      int,
                            NextPagePID     int,
                            PrevPageFID     int,
                            PrevPagePID     int)
go
INSERT #dbcc_ind
EXECUTE ('DBCC IND(2,474333853, 1) WITH TABLERESULTS, NO_INFOMSGS')
go

SELECT COUNT(*) FROM #dbcc_ind WHERE PageType =1

-----------
3

Tabela zajmuje 3 strony na dysku

Patrzymy jaki spid utworzył tabelę z trzema stronami

SELECT *
FROM sys.dm_db_session_space_usage t1
INNER JOIN sys.dm_exec_connections AS t2
ON t1.session_id = t2.session_id
CROSS APPLY sys.dm_exec_sql_text(t2.most_recent_sql_handle)
WHERE database_id = DB_ID('tempdb')
AND user_objects_alloc_page_count =3
AND t1.session_id > 50
ORDER BY 3 desc





Metoda ta jest niedokładna jednak przeważnie szukamy duzych obiektów a te już łatwiej wyłapać ze względu na duże ilości zajętych stron

piątek, 5 listopada 2010

TRUNCATE TABLE

Truncate table od strony dyskowej działa w bardzo ciekawy sposób

Robimy sobie tabelkę

USE tempdb
go
CREATE TABLE test (pole INT, tekst VARCHAR(20))
go
INSERT test SELECT 102,'test'

Sprawdzamy co jest na dysku, znajdujemy nr obiektu i bazy danych

select id AS obiectid, DB_ID() AS bazaid
from  sys.sysobjects
where xtype ='U'
and name like 'test'

obiectid    bazaid
----------- ------
1296892583  2


Wywołujemy dbcc żeby znaleźć adresy stron dla tabeli

DBCC IND(2,1296892583, 1) WITH NO_INFOMSGS



Interesuja nas strony typu 1, uruchamiamy flage pozwalająca na przeglądanie danych

DBCC TRACEON(3604) WITH NO_INFOMSGS
I oglądamy stronę

DBCC PAGE (2, 1,552360,3) WITH TABLERESULTS, NO_INFOMSGS



insertowane dane są na stronie, teraz robimy

TRUNCATE TABLE test
Uruchamiamy
DBCC IND(2,1296892583, 1) WITH NO_INFOMSGS
------
Command(s) completed successfully.

Brak adresów stron przyporządkowanych do tabeli, ale jeśli pamiętamy na których stronach były wcześniej

DBCC PAGE (2, 1,552360,3) WITH TABLERESULTS, NO_INFOMSGS



ups… dane nadal tu są do czasu aż serwer nie zamaże ich innymi danymi.
W przypadku delete lub drop table  nie ma wcale sekcji SLOT

środa, 3 listopada 2010

convert

Dzięki SQL Server 2008 życie administratora stało się prostszym, jeżeli kiedyś miałeś okazję przeprowadzać jakieś zabawy z zapisem binarnym to wiesz o co mi chodzi. W wersjach niższych niż 2008 funkcja convert przy zamianie typu varbinary na varchar dawała mylące wyniki

DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=@bin
SELECT @char,@bin

Zapytanie bez jawnej konwersji zwraca

---------------------  -------------------
super                    0x7375706572

Dodanie convert niewiele zmienia


DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=CONVERT (VARCHAR(100),@bin)
SELECT @char,@bin

---------------------  -------------------
super                    0x7375706572

Sam Microsoft zalecał dodanie procedury sp_hexadecimal przy używaniu procedury do przenoszenia loginów http://support.microsoft.com/kb/246133

Jest sporo metod obejścia tego problemu np.

DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char= '0x' + CAST('' as xml).value(
  'xs:hexBinary(sql:variable("@bin"))',
  'VARCHAR(MAX)')   
  SELECT @char,@bin

---------------------  -------------------
0x7375706572           0x7375706572

Są systemowe funkcje

DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=sys.fn_varbintohexstr(@bin)
SELECT @char,@bin

---------------------  -------------------
0x7375706572           0x7375706572

DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=sys.fn_varbintohexsubstring(1,@bin,1,0)
SELECT @char,@bin

---------------------  -------------------
0x7375706572           0x7375706572

Podobnie jest w druga stronę, przy zamianie varchar na varbinary funkcja convert traktowała wsad jako znaki ASCII i konwertowała je jeszcze raz

DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SET @bin=@char
SELECT @char,@bin
-----------------
Msg 257, Level 16, State 3, Line 4
Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

Niejawna konwersja nie udawała się, dodanie convert mogło sporo namieszać

DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SET @bin=CONVERT(VARBINARY(100),@char)
SELECT @char,@bin

----------------- ----------------------------------
0x7375706572       0x307837333735373036353732

Można to było obejść

DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SELECT @bin=CAST('' as xml).value(
  'xs:hexBinary(substring(sql:variable("@char"),
                    sql:column("A.position")))',
  'VARBINARY(MAX)')
FROM (SELECT CASE SUBSTRING(@char, 1, 2)
             WHEN '0x' THEN 3
             ELSE 0
             END) AS A(position)
SELECT @char,@bin

---------------------  -------------------
0x7375706572           0x7375706572

Lub funkcją systemowa


DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SET @bin=sys.fn_cdc_hexstrtobin(@char)
SELECT @char,@bin

---------------------  -------------------
0x7375706572           0x7375706572

W SQL Server 2008 doszedł styl konwersji który pozwala otrzymywać poprawne wyniki

DECLARE @char VARCHAR(100)
DECLARE @char2 VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @bin= CONVERT(VARBINARY(100),'super')
SET @char=CONVERT (VARCHAR(100),@bin,1)
SET @char2=CONVERT (VARCHAR(100),@bin,2)
SELECT @char,@char2,@bin

--------------  ------------ --------------
0x7375706572    7375706572    0x7375706572

DECLARE @char VARCHAR(100)
DECLARE @bin VARBINARY(100)
SET @char= '0x7375706572'
SET @bin=CONVERT(VARBINARY(100),@char,1)
SELECT @char,@bin
---------------------  -------------------
0x7375706572           0x7375706572

wtorek, 2 listopada 2010

xp_fixeddrives

Nieudokumentowana procedura xp_fixeddrives jest bardzo przydatna w pracy administratora, tym bardziej że sam cmd nie dostarcza nam sposobu na proste wylistowanie ilości wolnego miejsca na dyskach. Systemy linuksowe mają swoje df –h w windowsach trzeba stosować sztuczki.
Procedura rozszerzona (extended stored) jest częścią biblioteki xpstar90.dll, nie zaleca się stosowania jej w rozwiązaniach produkcyjnych ponieważ może zniknąć w następnych wersjach.
Procedura wywołana bez parametrów zwraca informację o dyskach

EXEC master.dbo.xp_fixeddrives

drive MB free
----- -----------
C     21175
D     69100
E     74389


Można ją wywoływać z parametrem liczbowym,
z 1 nic nie pokaże,
z 2 nic nie pokaże, prawdopodobnie odpowiada ona za dyski wymienne
z 3 nic nie pokaże,
z każdą inną zwróci informacje o dyskach.

We wcześniejszych wersjach sesja z parametrem 2 zawieszała się i nie pozwalała zamknąć.
Według wytycznych bezpieczeństwa powinniśmy odinstalowac ta procedurę lub odebrać prawo do jej uruchamiania, domyślnie ma ją grupa public.
Wywoływanie tej procedury na prawach innych niż sysadmin może zwracać nieoczekiwane rezultaty


CREATE LOGIN slaby WITH PASSWORD=N'test', CHECK_POLICY=OFF
GO
USE tempdb
GO
CREATE USER slaby FOR LOGIN slaby
GO


SET NOCOUNT ON

SELECT SYSTEM_USER,USER_NAME()
go
EXEC master.dbo.xp_fixeddrives
go

EXECUTE AS LOGIN ='sa'
GO

SELECT SYSTEM_USER,USER_NAME()
EXEC master.dbo.xp_fixeddrives
REVERT
GO

EXECUTE AS LOGIN ='slaby'
GO

SELECT SYSTEM_USER,USER_NAME()
EXEC master.dbo.xp_fixeddrives
REVERT
GO

EXECUTE AS LOGIN ='domena\slaby'
GO

SELECT SYSTEM_USER,USER_NAME()
EXEC master.dbo.xp_fixeddrives
REVERT

---------------------- -----------
domena\administrator   dbo

drive MB free
----- -----------
C     21175
D     69100
E     74389


---------------------- -----------
sa                     dbo

drive MB free
----- -----------
C     21175
D     69100
E     74389


---------------------- -----------
slaby                 slaby

drive MB free
----- -----------
C     18215
D     53156
E     71061


---------------------- -----------
domena\slaby          domena\slaby

drive MB free
----- -----------
C     21175
D     69100
E     74389


Login slaby dostał inne wyniki niż np. domena\slaby, nie sądzę żeby przyczyną był brak praw bo wtedy nie dostałby zupełnie wyniku.
Co dziwniejsze sytuacja daje się powtórzyć tylko na niektórych serwerach, niczego dziwnego w ich konfiguracji nie znalazłem.

Wyniki dla pozostałych loginów zgadzają się


22 204 411 904 byte = 21 175,7773 megabyte