SQL Server Performance Tuning — Top 10 sessions caused blocking SELECT TOP 10 r.session_id, r.plan_handle, r.sql_handle, r.request_id, r.start_time, r.status, r.command, r.database_id, r.user_id, r.wait_type, r.wait_time, r.last_wait_type, r.wait_resource, r.total_elapsed_time, r.cpu_time, r.transaction_isolation_level, r.row_count, st.text FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st WHERE r.blocking_session_id = 0 AND …
Deadlocks in Azure SQL Database
Deadlocks in Azure SQL Database : Recently we were working with Azure Logic Apps to invoke Azure Functions. By Default, Logic App runs parallel threads and we didn’t explicitly control the concurrency and left the default values. So Logic App invoked several concurrent threads which …
Memory-Optimized Tables
Monitor Memory Optimized Table Space Usage : ; WITH system_allocated_memory ( system_allocated_memory_in_mb ) AS ( SELECT ISNULL(( SELECT CONVERT(DECIMAL(18, 2), ( SUM(TMS.memory_allocated_for_table_kb) + SUM(TMS.memory_allocated_for_indexes_kb) ) / 1024.00) FROM [sys].[dm_db_xtp_table_memory_stats] TMS WHERE TMS.object_id <= 0 ), 0.00) ), table_index_memory ( table_used_memory_in_mb, table_unused_memory_in_mb, index_used_memory_in_mb, index_unused_memory_in_mb ) AS …
Install SQL Server Docker Container on Linux
Install SQL Server Docker Container on Linux and Connect through Client Tools I was surprised by ability to run setup and run SQL Server Docker container. It only takes about 2 commands and 2 Minutes. $docker pull microsoft/mssql-server-linux $docker run -e ‘ACCEPT_EULA=Y’ -e ‘SA_PASSWORD=Password’ -p 1433:1433 …
SQL Server Optimizations for High Concurrency
SQL Server Optimizations for High Concurrency Our business needs very robust, low latency, highly available and durable online transactional system which supports high concurrency for about four weeks in a year. It’s almost like Thanksgiving sale where you mark down very popular item (think of iPhone ) by …
Caching SQL Server Tables After Restart
SQL Server Cached Pages and ways to bring data pages to memory
We noticed considerable latency in stored procedure execution after restarting SQL Server Instance which was triggered by accessing data from disks instead of memory. So we ended up writing a simple script to bring data pages to memory. This helped us to avoid first touch penalty after restart in production environment.
Launching AWS SQL Server RDS
Learning from Launching AWS SQL Server RDS
We are evaluating options to migrate our read/write heavy production SQL Server database to amazon SQL Server RDS. We have pretty high throughput needs for few hours a day for few months in a year ,which is mission critical for our business success. We are evaluating pros and cons of moving to amazon RDS with provisioned IOPS. Current hosted provider doesn’t offer on-demand scaling solution .
AWS RDS vs Azure vs Managed Services for SQL Server
We are evaluating pros and cons of different hosting solutions for SQL Server which best suits our business needs.
Our plan is to evaluate AWS SQL Server RDS, Azure RDS , Managed solutions from hosting provider.
Evaluate each option in these categories.
1. Performance and Reliability
2. Ability to scale up during peak loads
3. Cost ( Based on Network , Storage, Memory and CPU )
4. Operations Efficiency
5. Compliance
Remove Procedure Cache and Reset Wait Stats
Remove Procedure Cache and Reset Wait Stats Remove all elements from the plan cache for the entire sql server instance DBCC FREEPROCCACHE; Flush the cache and suppress the regular completion message DBCC FREEPROCCACHE WITH NO_INFOMSGS; Remove all elements from the plan cache for a specific …
SQL Server Hardware Selection
SQL Server Hardware Selection , licensing cost and BenchMark Tools.