Tag Archives: TSQL

MS SQL Notes – Database Management Scripts

最近有機會去接觸公司的Database
可以幫忙performance tuning
很可惜在之前的公司沒有好好的記下一些有用的script
今日想和大家分享一些有用的Database Management script
和用來check Database 的東西
以下有兩個網頁
https://ola.hallengren.com/

這個提供了一些SQL Server Backup, Integrity Check, and Index and Statistics Maintenance的script/解決建議
Script 可以在這裡下載
https://ola.hallengren.com/scripts/MaintenanceSolution.sql
SQL Server Maintenance Solution


http://whoisactive.com/
提供了script來幫助 Monitor SQL Server
去到網頁入面便可以download之後在 SQL Server Management Studio上執行便可以安裝這些script了
sp_whoisactives

有時間再和大家介紹他們的use case

Hope you find it useful

TSQL Get size of all tables in database – TSQL 查詢Table佔用了多小空間

在整理公司的Database 其間發現公司的其中一個Database 的 MDF 檔 799GB 大
之後發現入面有很多用來做Backup的Table
是Developer 有時貪方便
便使用 INSERT INTO z_TableName
來Backup Table 之後便留下了很多的佔有空間的Table 又未必有用 便在這個Database 上

或些有時候有些Table存有很多 舊的Data..又可能不常用
所以希望找一個Query來 查詢在Database上的Table佔有多小空間 和用了多小Row
有多小空間 Provision了 但是沒有用.. 那我們可以考慮使用一些 TSQL CommandReclaim/釋放 那些空間

解決方法十分簡單
我們可以使用這個Query便可以了

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8/ 1024.00 AS TotalSpaceMB, 
    SUM(a.used_pages) * 8 /1024.00AS UsedSpaceMB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8/1024.00 AS UnusedSpaceMB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

TSQL Show All space used in Database per table
Hope you find it useful

Microsoft SQL Server Check If Full-Text Search is Installed – 如何查詢 SQL Server上的 Full Index Search 是否已經安裝

最近忙於將公司的 Database由舊的Server Migrate到新的 Environment
舊的DBA當然很小心地沒有留下任何 Notes 他怎麼設定舊的Database
所以這次Migration有很多東西都要自己摸索

在設定時安裝程式問..要不是安裝 Full Text Search..
我真是不清楚舊的Server有沒有安裝.. 只是知道沒有使用過這個Feature
做了一會research之後發現
其實是可以用Query來查詢這些設定的
我們可以使用以下我Query來查詢 SQL Server上的 Full Index Search 是否已經安裝

SELECT FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

SQL Server Full Text Search Info
以下的Query可以提供更多的資訊

SELECT * FROM sys.fulltext_catalogs

Hope you find it useful

TSQL Get Index Size Per Table – 如何使用TSQL 來查詢每一個Table上 每一個Index 佔用的空間大小

今天想和大家分享如何使用TSQL 來查詢每一個Table上 每一個Index 佔用的空間大小
這個可以令大家明白Database 的空間有多小放在Index上
明白更多..應該有幫助吧

解決方法
我們可以使用以下的Query 來查詢 每一個Table上 每一個Index 佔用的空間大小

SELECT
OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
i.index_id AS IndexID,
8 * SUM(a.used_pages) AS 'Indexsize(KB)',
8 * SUM(a.used_pages) / 1024.00 AS 'Indexsize(MB)'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
GROUP BY i.OBJECT_ID,i.index_id,i.name
ORDER BY OBJECT_NAME(i.OBJECT_ID),i.index_id

TSQL Get Individual Index SizeHope you find it useful

SQL Server Check last accessed Database Table – 如何找出Database 的Table上最後被查詢的時間

公司上有很多 Legacy / 舊有的Database 由于同事離開始於時沒有好好交代
所以便有很多在Database Server上存在的Database 但是又沒大人知道有沒有任何Application 會連接的

今天想和大家分享一個SQL Script有來查詢某一個Database最後一次被就Query/Update的時間
解決放法十分簡單

首先我們需要先選擇想查詢的Database
之後我們可以使用 以下的SQL Script 來看看 last_user_seek, last_user_scan, last_user_lookup 的內容
看看他們的更改時間

SELECT    OBJECT_NAME(object_id) AS TableName,
last_user_update, last_user_seek, last_user_scan, last_user_lookup
FROM    sys.dm_db_index_usage_stats
WHERE    database_id = DB_ID()

SQL Server check which is the Last Accessed Table
Hope you find it useful

TSQL Case Sensitive Query – TSQL 如何寫一條Query可以做一個Case Sensitive match

今日公司有一個程式出現了一個問題
內容大概是這樣的 “Incorrect syntax near the keyword ‘and’
TSQL Incorrect syntax near the keyword
程式是用來執行一些使用者寫個SQL…
我們這個程式當作 Data Integrity Checker
誰不知有使用者在更新這個Table上的 SQL 寫了一些有Syntax Error
SQL Query 沒有檢查便儲存了
嘗試使用Query去找出那些SQL Query‘and’這個字
但是由於之前設定 Database 時設定了 TSQL 是 Case Insensitive的關係
所以便出現了很多 有 And的 record出現 E.G. ‘AND’, ‘And’, ‘and’ 等等

那麼怎樣可以寫一條Query可以做一個Case Sensitive match
做了一會research之後發現 原來解決方法十分簡單

解決方法:
我們只需要 在 WHERE Clause上 要比較的 Field上使用COLLATE Latin1_General_CS_AS [一個Case Sensitive的 collation/ 排序規則] 便可
E.G.

SELECT *
FROM DataIntegrityTable
WHERE IntegritySQL LIKE '%and%' COLLATE Latin1_General_CS_AS

Hope you find it useful

How to update default language from SQL Server – 怎樣更改 SQL Server的預設語言

在安裝SQL Server 其間好像沒有地方可以給我們更改 SQL Server的預設語言
所以每當我們建立新的Database的時候都會用安裝時預設的語言 E.G. English [US English]

如果想了解怎樣找到當時使用者在 SQL Server 或這個Database 上的 語言可以到以下的網誌參考
TSQL Get Current SQL Server language – 使用TSQL 取得現在的SQL Server 使用中的語言

當我們需要更新現有Database的預設語言 我們會用到這一個 指令

EXEC sp_configure 'default language', [Language ID] ;
RECONFIGURE

但是怎樣可以找到 SQL Server上的 Language ID
大家可以執行以下的 TSQL 指令

SELECT *
FROM sys.syslanguages

SysLanguages on Microsoft SQL Server
當找到想更新到的 Language ID 時大家可以執行 sp_configure去更新 Database / 建立新用戶時的預設語言
在公司需要使用的 預設語言是 British English
所以我要執行的 TSQL 是這樣的

23 是British English

EXEC sp_configure 'default language', 23 ;
-- 之後再執行 Reconfigure 去安裝這個設定
RECONFIGURE

Hope you find it useful