FastSqlServer.com - SQL Server Consulting - Performance Consultant

Go fast with SQL Server 2019 performance

• Batch Mode on Rowstore
• Memory-optimized tempdb metadata reduces PAGELATCH_UP waits
• Row mode memory grant feedback - prevent spills to tempdb disk
• Scalar user defined function inlining
• Table variable deferred compilation solves the 1 row estimate problem
• APPROX_COUNT_DISTINCT when you only need an approximate count
• Scheduler worker migration - balance CPU usage for long running tasks

• Updatable non-clustered Columnstore Indexes
• More use of Batch vs Row mode when Columnstore is used
• Combine non-clustered indexes with clustered Columnstore indexes
• Readable Columnstore indexes in readable AG secondaries - a big deal
• Columnstore index performance improvements
• In-Memory table improvements
• Lower latency AlwaysOn Availability Group replication

• Clustered Columnstore indexes - fast analytics queries
• Buffer Pool Extension - extends RAM cache to fast SSD
• Enhanced online indexing - varchar(max) - WAIT_AT_LOW_PRIORITY
• New cardinality estimator - good and bad
• In-Memory tables - no locks, hash index
• AlwaysOn Availability Groups for availability and reporting servers

• Window Function Enhancements - faster aggregate computations
• Showplan Enhancements - more performance details of execution plans
• Columnstore Indexes - high speed queries for read only data
• Increased Extended Event Capabilities - many more events

• Data Compression - Row and Page compression reduce disk I/O speeding up scans
• Backup Compression - Backup sizes up to 1/5 as large and much faster
• OPTION(RECOMPILE) - Enhanced parameter embedding optimization as of SQL Server 2008 SP1 CU5
• MERGE T-SQL Statement - Perform Insert/Update/Delete in a single T-SQL statment
• Table Valued Parameters - Efficiently pass an array of data to SQL Server
• Integrated Full Text Search - Solves issues with queries mixing CONTAINS and other WHERE conditions
• XEvents - A new tool to monitor performance at a more detailed level
• Star Join Query Optimization - The optimizer now improves performance of this common query pattern
• Grouping Sets - Define multiple groupings in a single T-SQL statement to reduce table scans
• HierarchyID Data Type - Search tree structured data faster with this new data type
• DATE Data Type - More efficient queries are possible using the new DATE data type
• FILESTREAM Data - Store very large data blobs more efficiently directly on the file system
• Partitioned Table Parallelism - Improved multi-cpu performance for queries processing multiple partitions
• Spatial Data Type - Fast responses for spatial data queries such as "Which stores are near my location?"

• Fewer recompiles - Query plans in stored procedures are now maintained per SQL statement.
• Index INCLUDE columns - A great 2005 feature to speed up many more high read queries.
• Dynamic Management Views - Detailed performance data to make your system run fast.
• 64 bit - Break the RAM barrier - Standard and Enterprise editions: 8GB, 16GB, 32GB, 64GB, 128GB.
• New Profiler and graphical query plan features - Great tools to solve problems faster.
• New Profiler deadlock details and blocked process reports - Find the root causes fast.
• On-Line index builds - Add and rebuild indexes without downtime to solve problems fast.
• Database Mirroring - Maintain an up-to-date standy server for fast disaster recovery.
• Plan Guides - Fix problem queries with hints but without having to modify T-SQL
• Tranisitive relationships - Query plans take advantage of transitive column relationships.
• C# and VB.NET - Replace CPU intensive T-SQL processing with faster .NET CLR code
• Table and index partitioning - Partition historical data for top performance.
• Newsequentialid() - Generate GUIDs that don't fragment clustered indexes.
• Want more? I use a long list of useful 2005 performance features to solve your problems.

Most IT managers are skeptical of SQL Server performance improvement claims.
FastSqlServer clients have seen their applications run fast first hand! Those IT managers got the positive impact for their daily business operations. Developers were happy to see their software running at top speed. Inquire for references. Talk to management and technical people who have witnessed dramatic increases in the speed of SQL Server and their business systems. Start on the road to fast SQL Server performance today.

Chris Dickey
San Diego, California
info@TuneSqlServer.com
I have assisted clients across the USA

• Andrew: "All good Chris!!!! I think you've slayed this dragon..."
• Frank: "I think we may have to hang your picture here. They are telling me you worked a miracle. Everything is incredible! I really appreciate your help."
• Gil: "The knowledge the team gained from you gave them ideas on how to further improve things. The overall Navitaire environment is now processing more than 100,000,000 transactions per day across our global infrastructure, with consistently high SQL Server performance and availability in the 99.95% range."
• Keith: "We were ready to buy a new top of the line, multi-thousand dollar server when we happened upon Chris and his services. After Chris was done, we not only saw a performance increase as much as 1400 percent in one of our often-used queries, but forestalled the need for the new server even a year later. Our CEO still says the money spent on Chris was our best purchase in 15 years of doing business."
• Gary: "Chris, all I can say is WOW!!!! You are unbeleiveable, I feel that we have a new application already. Customers I spoke with today are amazed how fast everything feels."
• Christian: "When I think SQL performance problems, I think Chris Dickey."
• Tony: "Also a quick thanks for the amazing job. The speed and performance increases are literally jaw-dropping..."
• Andrew: "Our SQL looks awesome now, the CPU utilization... It's down to 20% now..."
• Andy: "Hey Chris...we put all your stored proc changes in last night. Honestly, I've never seen the site run this fast...absolutely amazing. Every screen is loading in less than .5 seconds. My team is amazed. Feel free to add this as a testimonial to your website."
• Chris: "Awesome. Thanks for the quick fix on that. You saved us thousands of dollars and a lot of man hours. We were going to dump that system for something else."
• James: "After months of struggling with our interface DB performance and having regular deadlocks, Chris Dickey came in and within 3 days improved our performance by 200%. After spending less than 60 hours on our environment our total improvement was well beyond 400% and we experienced no more deadlocks. Needless to say we have forwarded Chris' contact information to companies that we knew had similar challenges."
• Patrick: "Fixed, ran ninjaly faster."
• Mike: "We are very happy with the improved performance as well as the processor levels (they are way down still)."
• Richard: "The CPU levels look a lot better today on the production server. I implemented your changes early this morning and we have ~80 concurrent users with an avg cpu usage at ~50%; which is a lot less then yesterday with this number of users. I have been on the website with the 80 or so users and I have noticed it is a lot faster."
• George: “I just wanted to thank you for pin pointing the issue for us. You truly know your SQL. You have worked wonders with our SQL Server and it’s comforting to know that you can be relied upon to keep it running in tip-top shape.”
• Rob: "Most CWs are looking for the long engagement, with Chris we got immediate performance results (within a couple days). I really can't say enough good things about him. Obviously performance gains will vary with every situation, but our CPU levels were hovering around 80% on an 8 proc Xeon box during peak usage, within days it was down to 20% average CPU usage, and now is around 10-15%. User experience on our site was similarily improved."
• Thomas: "Thank you so much for helping us and guiding us so our SQLserver spins like a Formula one racing car."
• Allen: "Yes we moved to sql 2005 and the speed is absolutely incredible. All loans all day long price under 30 seconds. Some scenarios are under 10 seconds. We have customers specifically calling or emailing to say whatever we did to keep it up, the speed is unbelievable."
• David: "I really can see a significant difference in the performance of the database. Some areas of BOSS are much, much faster. It's the way it should always have been. Nice work."
• Keith: "Your work and feedback to this point has been instrumental in the stability that we have on the db side of our products."
• Carl: "Yes Chris, Joe changed the index and deadlocks have not happened since!"
• Mark: "I checked this evening's BOM Regen job. There is often variation - some nights 2 hours, others 3, and occaisonally 10 hours. Tonight's job completed in 12 minutes - I am sure that is related to the new index. Again, we've seen dramatic improvement in invoice printing, MRP, and BOM Regeneration. One more positive note - no deadlocks this week."
• Dan: The reports this morning regarding timeouts from the application are down. CPU overall usage is 1/3 of what it has been on a Monday morning. I have told Kevin that you are a key asset mate.
• Bruce: "The performance problems we were having with SQL Server were severely affecting one of our large web applications. Your quick but methodical assessment identified several large problems and your solutions turned an emergency situation into one that could be managed."
• Mike: "Yes, the CPU levels look significantly lower that what we had on the 2000 server. We're running between 10-15% CPU, where before we were running at 30+%!!! An amazing improvement. How much of that has to do with 2005 or with the work you accomplished is unclear, but either way things are running great."
• Hung: "Thanks again for your help. We're still humming along after you performed your magic."
• Ray: "The improvement you made for that process was so drastic that when someone was out sick and they had less man power to do the time cards they still finished early."
• Robert: "Here's what our Vice President had to say: I can't tell you how pleased I am that the database issues that have been plaguing us are finally getting tamed!"
• David: "The web sites are more responsive than they've ever been, even before our strong surge in usage. We can finally celebrate the surge in usage instead of holding our collective breath over how the sites will handle it."
• Your Name Here: