SQL Server에서는 IS NULL 조건자와 함께 작성 하는 필터링 된 인덱스를 사용 하지 않습니다. 필터드 인덱스 사용시 IS NULL 조건자의 경우 include에 컬럼 추가되어야 함
예) 필터드 인덱스 조건 중 IS NULL의 경우 include 컬럼 항목
CREATE NONCLUSTERED INDEX New_i_action_filt_action_date_type ON dbo.filter_test (action_type) include (action_date) WHERE action_date IS NULL
Because this data is available in real time without needing to wait for the query to complete, these execution statistics are extremely useful for debugging query performance issues.
SQL Server 2016 Community Technology Preview 2 Evaluations
새로운 버젼이 나왔네요...
2014 버젼도 쓰지 않은데, 2016버젼까지 나왔으니... 에휴에휴
As the biggest leap forward in Microsoft’s data platform history, SQL Server 2016 delivers
breakthrough mission critical performance, deeper insights across any data on many devices
and enables the power of hyperscale cloud to unlock new hybrid scenarios.
• Operational Analytics: Insights on operational
data, works with in-memory OLTP or on disk
• In-memory OLTP enhancements: Greater
T-SQL surface area, terabytes of memory
supported and greater number of parallel CPUs
• Query Data Store: Monitor and optimize query
plans with full history of query execution
• Native JSON: Parsing & storing of JSON as
relational data & exporting relational data to
• Temporal Database: Track historical changes
For SQL Server 2014, the algorithm is now:
- Is the growth size less than 1/8 the size of the log?
- Yes: create 1 new VLF equal to the growth size
- No: use the formula above
So on SQL Server 2014, if you created your log at 1GB and it auto-grow in chunks of 512 MB to 200 GB, you’d have:
- 16 VLFs from the initial log creation
- All growths up to the log being 4.5 GB would use the formula, so growths at 1, 1.5, 2, 2.5, 3, 3.5, 4 GB would each add 8 VLFs * = 56 VLFs
- All growths over 4 GB will only create 1 VLF per growth = (200 – 4) x 2 = 392 VLFs
- Total = 392 + 56 + 16 = 464 VLFs
464 is a much more reasonable number of VLFs than 3200, and will be far less of a performance problem.
sp_whopro™ Script - SQL Server Activity Monitoring and Logging Stored Procedure