FastSqlServer.com - SQL Server Consulting - Performance Consultant

Go fast in days, not weeks! SQL Server Performance you need to succeed.
Email: info@TuneSqlServer.com info@FastSqlServer.com
SQL Managed Instance / 2022 / 2019 / 2017 / 2016 / 2014 / 2012 R2 / 2008 R2 / 2005 / 2000

Who are my Clients?

Chris Dickey can make your applications run faster: Measure > Analyze > Improve.
22 years of SQL Server performance consulting experience.
I can set up SQL Server in Azure for your business. Azure SQL Managed Instance or SQL Database
The list of SQL Server performance killers is long. I provide fast solutions to problems you will encounter.
Contact me to discuss tuning your SQL Server or deployment to Azure.
Email: info@TuneSqlServer.com info@FastSqlServer.com

Gary: "All I can say is WOW!!!! Customers I spoke with today are amazed how fast everything feels."
Andrew: "Our SQL looks awesome now, the CPU utilization... It's down to 20%."
Tony: "The speed and performance increases are literally jaw-dropping."
Christian: "When I think SQL performance problems, I think Chris Dickey."
Chris: "Awesome. You saved us thousands of dollars and a lot of man hours."
Andy: "Honestly, I've never seen the site run this fast...absolutely amazing. My team is amazed."
Thomas: "Thank you so much for helping us and guiding us so our SQL Server spins like a Formula one racing car."


Chris Dickey finds and eliminates the true SQL Server performance killers causing pain!
Why SQL query optimization is hard - David DeWitt - Microsoft Jim Gray Systems Lab

FYI: A user defined scalar function in a query limits it to 1 cpu - no parallel plan
FYI: option(recompile) does a lot more than most people realize
FYI: With lots of RAM a parallel plan with a bitmap filter can provide great performance.
FYI: Use SQL Server AlwaysOn for redundancy and read only secondaries.

My SQL Server background and training


What might be causing high CPU levels and long durations on your system?
Get Fast SQL Server performance you are chasing after - in days!
View the FastSqlServer Client List
SQL Server runs fast with expert tuning. Don't settle for guesses you find on Internet web sites. I provide accurate answers that target the true causes of sql server slowdowns. Faster SQL Servers have increased business and lowered costs for my clients.

Don't postpone performance ills with hardware pills. Contact FastSqlServer today!
SQL Server 2019, 2017 and 2016 still require index and T-SQL adjustments driven by brainpower to run at top speed.
SQL Server 2019 features   SQL Server Performance Killers   Bad things happen when you ...

You can do a lot better without spending more money on RAM, CPUs or tools.
FastSqlServer clients will tell you. SQL Server can deliver data extremely fast. When SQL Server runs at top speed, your business efficiency and profits increase. Don't waste time and money searching for answers in T-SQL and client side application code. I will quickly find the true causes of why your system is slow and prioritize effective solutions. I use techniques that work from years of real world experience.
1. Do complaints about time-out errors keep you busy doing the wrong things?
2. Are your customers and employees frustrated with slow web pages caused by slow T-SQL?
3. Does your tech staff know what needs to be fixed first to get the most bang for the buck?
I supply the missing expert tuning skills to make SQL Server run at top speed.
Effective indexes and minimal T-SQL code changes are used to speed up your system. Advanced tuning skills are required to make SQL Server run fast and stable. It is not automatic yet. The causes of slowdowns, timeouts, high CPU, high disk I/O, blocking and deadlocks are accurately identified using Profiler, Extended Events, Query Analyzer and Dynamic Management Views.
Example: You have a slow query caused by an index scan problem. CPU usage is high but disk I/O is low. Buying more RAM won't speed up the query because the index is already cached in RAM. Faster CPU's might speed up the query by a factor of 2X with a faster index scan in RAM. With expert tuning you can eliminate the index scan to run the query 10 times faster.
How can SQL Server run faster?
Better indexes solve performance problems with no application changes. Systems go FAST with the right mix of multi-column indexes. Complex queries run slow if high cost execution plans are used because of wrong optimizer estimates. Index improvements and minor T-SQL rewrites solve those problems. Application T-SQL code problems cause timeouts: data type mismatches, SQL Server functions used wrong, table variables, table columns wrapped in functions, long duration locks, etc. My fixes to eliminate those problems have been proven to work by years of speeding up SQL Server systems for my clients.

Find out how little it takes to speed up SQL Server a lot!
I have tuned SQL Server to run fast for a lot of diverse businesses across the USA - View list of clients.
Is CPU utilization hitting the roof? Are disk I/O levels rising? Do you have problems with blocking, timeouts or deadlocks? Are you going to spend money on faster hardware? Give the expert tuning approach a shot at solving your problems.

Go fast with SQL Server 2019 performance - back to top
  • 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
Go fast with SQL Server 2017 and 2016 performance
  • 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
Go fast with SQL Server 2014 performance
  • 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
Go fast with SQL Server 2012 performance
  • 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
Go fast with SQL Server 2008 performance
  • 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?"
Go fast with SQL Server 2005 performance
  • 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.

What is a main cause of slow web sites and information systems?
Database servers running slower than they should - much slower. Why so slow?
Most SQL Server systems already have more than adequate hardware.
The true bottlenecks deserving attention are high I/O levels because of inadequate indexing and high cost query plans caused by poor query optimizer estimates. Too many hash joins and recompiles can also contribute to high CPU forcing long durations, blocking and deadlocks. Speed up your system by speeding up the few queries chewing up the majority of your system's resources. Contact FastSqlServer today. Get the fast results you need!


Contact Chris Dickey: info@TuneSqlServer.com
24 Years of SQL Server Experience - San Diego,California
FastSqlServer Clients - Performance Tuning Successes - Tuning Books - Links
Playing a Les Paul electric guitar for a change of pace
 
I tune SQL Server to run FAST!    >> >>> >>>>
Go fast in days, not months!

30 years working with SQL Server since 1989 release
I have a SQL Server 1.0 install 5 1/4 floppy disk!
22 year focus on SQL Server performance consulting
30 years of software: C++, C#, T-SQL, .NET, javascript
University of California San Diego graduate

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


Microsoft SQL Server

 >>> Clients Tell the Story of Success  >>>
  • 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:
What happens because the techniques needed to improve performance are not well known?
1. Performance seems to be OK: Today's hardware is so fast that the system seems to be running OK. It is often the case that tables are being scanned in RAM but nobody knows. There may be occasional time-outs or slowdowns during a few heavy usage periods when the scans cause excessive blocking. The business could be delivering data even faster and doing better than they realize. If the business prospers and the data in their database grows, they will eventually face query and report generation slowdowns and then big problems.
2. Live with it: There are many cases where CIO's, CTO's and owners just put up with a system they know is too slow. They think it would cost too much to improve their situation. DBA's and techies often give misleading information to management because they don’t really know what to do but they think they do or they want to look like they do. Upper management never gets the correct information that cost-effective solutions are available to speed up their information systems. These companies are not taking full advantage of the fast SQL Server database server that they own. They are wasting a valuable resource.
3. Run into the wall: Then there are the businesses that do run into the performance wall. This is often what happens to the "Performance seems OK" company after data in the database has grown past a certain level following business success. Fast hardware is no longer able to hide the underlying performance problems. Duct tape solutions are no longer working. We have all heard the stories of eCommerce sites that got "too many customers". There is so much confusion about the capabilities of SQL Server and how to improve its performance that most of those companies don’t know what to do. It is very common for them to assume or be told that they need more/faster hardware or need to switch to Oracle. The truth is that expert tuning is an extremely cost effective solution. We have seen big problems solved for many clients of all sizes with expert tuning. All of our clients had DBA's and tech people. They just didn’t have expertise in the specialty of SQL Server performance tuning. FastSqlServer consultants have provided the specialized know-how to achieve SQL Server performance success.
You have a hunch that SQL Server and your web site can do better!
That is the story that our clients have told us repeatedly. Often they are considering a new system when they contact us. Often they are desperate to fix time-out and deadlock problems. They sometimes think that SQL Server might not be good enough for their business needs. What a surprise they get after we speed up their server! Their data starts showing up lighting fast. Blocking and deadlock problems are gone. Customer complaints turn into satisfied customer feedback. And they didn't have to buy new hardware, rewrite their application or switch to Oracle. The tune-up ended up saving them lots of money. Their business potential increased with the faster web site and faster access to business data. Their technical staff got new SQL Server performance know-how. Techies, who at first were skeptical about working with expert SQL Server consultants, ended up happy to be working on a fast system. Confusion caused by misinformation posted at Internet SQL Server performance sites was replaced by technically correct information that does the job right. Company managers, presidents and owners realized that SQL Server really is a great database server that can power their business to success!
SQL Server has plenty of power to do a great job. Go Fast!
Certainly your database has grown beyond the size of small test systems. That is when performance problems start becoming apparent. One client didn't run into the performance wall until a heavily used table had grown from 12 million to 20 million rows. The solution to their most pressing problem was the removal of an index hint and the addition of a single column to an existing index. Another client who processes a 50GB and 100GB table every morning didn't realize that the daily processing time could be reduced by 50%. Their staff had been staying late until our performance tuning cut 4 hours of daily report generation time down to 1.5 hours. The 2.5 hours saved per day translated into earlier use of the database by the rest of the company's 100 employees - A big win! Another client was ready to give up on SQL Server 2000 when they tried to run 3 large multi-GB databases on a single server. Now they run over 10 databases with CPU to spare. That client now has lots of confidence in the SQL Server capabilities to host large eCommerce web sites. These success stories are typical, not unusual. Your current hardware is probably fast enough. Don't waste money on hardware you don't need! Get your system properly indexed and tuned first. Then decide if you really need faster and bigger hardware. The problem usually isn't the hardware.
I find the high cost queries hiding in the complexity of your system - I fix them!
You get big improvements without costly database or code modifications. Most companies already own adequate hardware. And, you also own high powered diagnostic tools: SQL Server Profiler and Query Analyzer. Don't rely on Database Tuning Advisor to solve your tough performance problems, its not that smart. Don't be confused by incorrect information posted on Internet sites about indexes and tuning. Take the advice of a 20 year Microsoft SQL Server expert who has solved problems on many large SQL Server systems. My advanced experience also includes many years implementing systems using ASP, ASP.NET, Windows Server NT/2000/2003/2008/2012 R2 and the IIS web server. We have what it takes to solve tough system wide problems that affect database servers and web site performance. Take advantage of our years of real-world know-how and investment in advanced SQL Server tuning skills. Contact a FastSqlServer expert today to performance tune SQL Server for your business.

FastSqlServer client success stories support our claim that reliable techniques will give you performance improvements that lead to higher company productivity and customer satisfaction. Web pages that used to take a long time to display start showing up instantly. Reports and decision making data are available much quicker. Your server hardware gets new CPU and disk I/O breathing room. Keep your customers happy and increase your bottom line. Make the smart investment to take full advantage of the SQL Server potential you already own. We guarantee results that make a big difference in a short period of time. And, we transfer know-how to your technical staff so they can respond quickly to future performance crisis.

Eliminate the costs of a slow system: lost customers, complaint emails, missed deadlines, techie fire fighting, approaches that don't work, lost sleep. Your web site has to serve up information faster than your competitors and keep customers coming back! Give your employees a fast SQL Server information system to do their jobs effectively and on time. Be more productive and get the data you need fast to meet deadlines without stressing out or staying late. Give me a try.
SQL Server Performance Tuning Successes For Our Clients
1. Web page display times reduced from over 5 seconds to milliseconds
2. CPU utilization dropped to below 20% - CPU was at 80%-100% - Inquire for references!
3. Money saved with improved SQL Server performance - How to save money
4. Customer complaints stopped - No more long web page waits to complain about!
5. Reduced daily morning data processing time from 4 hours to 1.5 hours
6. Report generation times reduced from 10 minutes to under a minute
7. Timeout failures, deadlocks, blocking and slow queries eliminated
8. Customers did not waste money on extra RAM or CPUs or throw money at Oracle
9. More enthusiastic and productive employees and tech staff using a faster system
10. Increased reliability of Transaction Processing Systems - no deadlocks or zombie connections
FastSqlServer / TuneSqlServer consulting clients - back to top

Performance Success Stories
Faster web pages and lower CPU - ImprovementDirect.com
A longer life for the existing SQL Server hardware at BeneTrac
Credit report processing time reduced at First American CREDCO
SQL Server Performance Killers - Got any? - back to top
High cost queries hiding in the complexity of your application!
  1. Not enough indexes - Not enough index columns - Too many of the wrong indexes
  2. No multi-column indexes expertly formulated for frequent high cost queries
  3. No clustered index - Or, tables clustered on the wrong column
  4. Inaccurate or missing column statistics - Skewed data - Bad optimizer estimates
  5. Inefficient use of SQL Server functions in WHERE clause
  6. Fragmented tables and indexes - No fill factors - Use DBCC showcontig and indexdefrag
  7. Using COALESCE in WHERE clauses for optional parameters - very bad execution plans
  8. Stored procedure recompiles - temp tables, SET statements, too many SQL statements
  9. Not enough stored proc recompiles - Queries stuck on the wrong query plan
  10. Not using stored procs for common complex queries with fixed plans
  11. Bad estimates because AND column data correlation is unknown by the optimizer
  12. Not taking advantage of Indexed Views with SQL Server Enterprise Edition
  13. Retrieving too much data - Not using selective WHERE clauses, using SELECT *
  14. Inefficient T-SQL statements - Functions wrapping WHERE columns, Not using virtual tables
  15. Hash joins with table/index scans because of incorrect high row count estimates - CPU
  16. Temp DB performance issues - PAGELATCH_UP waits - Table Variables use temp DB!
  17. Queries that are too complex for the optimizer to figure out the best plan
  18. OR conditions causing poor query plans - The optimizer may need help
  19. Database log files on the wrong physical disk - Inefficient device growth factors
  20. Unbalanced hardware configuration - Disk, CPU, RAM, Network I/O - Look at wait states
  21. Using SQL Server 2000 - I upgrade you to 2012 for huge immediate improvements!
  22. Not using connection pooling correctly - Leaving connections open for too long
  23. Incorrect use of ADO in ASP pages - Not implementing disconnected recordsets
  24. Unnecessary use of client server side cursors - high network I/O and high CPU
  25. Slow concatenation of HTML output in ASP and ASP.NET pages - Use .NET stringbuilder
  26. Inefficient T-SQL algorithms - Iteration instead of set operations - too much temp data
  27. Not using computed columns to implement complex conditions involving multiple columns
  28. Overuse of table variables - They can cause costly query plans because of invalid estimates
  29. DBA's using Perfmon instead of Profiler to troubleshoot SQL Server performance problems
  30. Not using a SQL Server performance expert to solve your tough problems fast

The Steps to Improve SQL Server Performance

FastSqlServer Consulting clients
SQL Server Performance Tuning Books
The FastSQLServer Difference: Advanced Training and 30 Years of Experience
Chris Dickey combines the experience of a top notch SQL Server developer and a Windows Server expert programmer to deliver proven results running SQL Server 2019 on Windows Server 2019. Chris Dickey has designed and implemented software for 30 years. After working with early relational database systems in the mid 80s, he started working with the first version of SQL Server in 1989 when it was released on the OS/2 1.3 platform by Microsoft/Ashton Tate/Sybase. He has worked with Windows NT since it was first released in 1992. His programming experienced started with the C and Fortran languages in the early 80s followed by C++, T-SQL, Windows Win32 API, VB 6.0 and now VB.NET, C#, ADO.NET and ASP.NET work. He has attended the Microsoft Professional Developers Conference (PDC) many times since 1992 to keep on top of the latest Microsoft SQL Server, Windows Server and .NET development technologies.
Advanced technical developer conferences Chris Dickey has attended.
Microsoft Windows Server MCSE exams first passed in 1996.
Chris Dickey is a graduate of the University of California, San Diego in Management Science and Systems Engineering
SQL Server Books and Related Topics


This web site developed and hosted by cdickey.net consulting



Perf Notes
procs
queries
requests

That's all for now