How to create and optimize SQL Server indexes for better performance

Indexes are paramount to achieving good database and application performance. Poorly designed indexes and a lack of the same are primary sources of poor SQL Server performance. This article describes an approach for SQL server index optimization to increase query performance.

March 19, 2018

How to optimize SQL Server query performance – Statistics, Joins and Index Tuning

Have you ever gotten a new computer, hooked it up and said: “this computer is blazing fast, I love it”? I have. A year from then, I was like “this computer is so slow, I need a new one”.

Performance is a big deal and this was the opening line in an article that was written on How to optimize SQL Server query performance. The initial article shows not only how to design queries with the performance in mind, but also shows how to find slow performance queries and how to fix the bottlenecks of those queries. I’d highly recommend reading the above article first because this one would give a lot more meaning but also because it’s an appendix to this topic.

February 28, 2018

How to optimize SQL Server query performance

Performance is a big deal. No matter if we’re talking about applications in which users click buttons to display data or if we’re writing a query directly into let’s say SQL Server Management Studio (SSMS). Nobody likes to click a button, go get a coffee and hope the results are ready. As computers get faster and technology moves forward users get more impatient and want things right now; without having to wait.

January 23, 2018

Documenting individual build changes (vs an entire database) in a SQL Server continuous integration pipeline

Continuous integration is great, and the more iterations, the greater it is. But with all of those changes being integrated it can be easy to lose track of what has actually changed, and when, not if, something fails reconstructing a documentation trail can be a challenge.

If a label is being created with each build, then the label “image” can be restored, depending on the source control system, or even better, you can use ApexSQL Diff to compare the latest label with the previous and see in detail exactly what changed.

But with more frequent iterations (and CI processes are considered optimal when they are triggered as frequently as possible, even on each commit), creating a label for each individual commit, or even a small group of changes, isn’t practical. As changes pile up, it can be even more difficult sorting through the change logs for auditing or diagnostics purposes.

January 18, 2018

How to create and use DML triggers in SQL Server using real world examples

First thing first, a little background on triggers. Although often maligned, in certain circumstances they can be very helpful… once you get to know them. For example, sending an email from SQL Server could be done with a trigger based on some anticipated event.

In this article, we will focus on a case with T-SQL data modification when an Insert, Update or Delete statement hits the data in our table. As the name applies the trigger will fire and then we can make decisions on what next based on new data that is coming in or old data that is getting replaced. This is what triggers are most used for.

January 12, 2018

How to monitor and detect SQL Server round-trip performance issues using custom designed metrics

For every query issued by the application, time is needed to reach the SQL Server and then the time needed for results to get back to the application. As all communication between an application and SQL Server goes via some network (LAN or WAN), network performance could be a critical factor affecting overall performance. Two factors affect network performance: latency and throughput.

December 8, 2017

How to update a SQL database from source control while avoiding dependency errors

Working with a database under source control has many benefits. Beside tracking all the changes made against a database, including the information what the changes were and who made them, you can also track history of committed versions of all database objects which can be restored on a database at any point.

However, one of the benefits which will be explained in this article is comparing and syncing two copies of the same database. Using ApexSQL Source Control, all objects that are missing in one copy of a database can be created/restored in the second copy of a database, preserving the integrity of both databases.

December 7, 2017

How to add self data change auditing capabilities to commercial, shrink-wrapped software with a SQL Server backend

There are thousands of distributed. commercial software applications aka shrink-wrapped, from small shareware apps to large corporations, that use a SQL Server backend. These systems, although distributed, could often benefit from and sometimes require data change auditing at the client location.

Although ApexSQL sells a successful, enterprise auditing tool for SQL Server, ApexSQL Audit, this tool is often not appropriate for distributed, commercial software.

November 29, 2017