Index 조각화된 상태 조회 select a.index_id, name, avg_fragmentation_in_percent from sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('dbo.TABLE_NAME'), NULL, NULL, NULL) AS a inner join sys.indexes AS b on a.object_id = b.object_id and a.index_id = b.index_id Dev::SQL Server 2010.11.30
Index Rebuild, Reorgnize alter index all on TABLE_NAME REBUILD WITH (ONLINE = ON) alter index all on TABLE_NAME reorganize Dev::SQL Server 2010.11.30
I/O 비용이 가장 높은 Query 조회 SELECT TOP 10 [Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count ,[Total IO] = (total_logical_reads + total_logical_writes) ,[Execution count] = qs.execution_count ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_.. Dev::SQL Server 2010.11.30
CPU 비용이 가장 높은 Query 확인 SELECT TOP 100 [Average CPU used] = total_worker_time / qs.execution_count ,[Total CPU used] = total_worker_time ,[Execution count] = qs.execution_count ,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) ,[Parent Query] = qt.tex.. Dev::SQL Server 2010.11.30
SQL Server 2005 EXEC sp_helpdb; EXEC sp_tables @table_type = "'TABLE'"; # DB Backup USE master BACKUP DATABASE sqlDB TO DISK='C:\sqlDB.bak' # BETWEEN select name from data where age BETWEEN 10 AND 20 # IN select name from data where age in (11, 12, 13) # SubQuery select name from data where age >= (select age from area_user where code = '01') -- SubQuery가 2개 이상의 결과값을 가져온다면, 비교연산자를 쓸 수가 없다. -- => 해결방안 ANY 또는 SOM.. Dev::SQL Server 2010.05.03
NULL 데이터가 있는 table 조회 where 조건 검색 시 NULL 데이터는 검색 조건에서 빠진다. Ex. # table - UserData UserID DataID ---------------------------------- User1 NULL User1 ID0 User1 ID1 select * from UserData where DataID 'ID1' => 결과는 User1 ID0 select * from UserData where DataID is null or DataID 'ID1' => 결과는 User1 NULL User1 ID0 ==> NULL 데이터있는 데이터 검색 시 주의 Dev::SQL Server 2010.04.30