Depending on your particular environment, database reporting can have a heavy impact on the database performance, can execute queries which run for dozens of minutes or both. This is usually the case with reports which require complex queries having multiple calls to SQL Server’s aggregate functions to be executed against very large data sets. The effect on the database performance is particularly severe in scenarios where the production database stores data which is changed often – as data modification operations require exclusive locks, in order to preserve the integrity of the database, SQL Server will go ahead with the data modification operation until the query initiated by the report is still running. This increases the chances of a deadlock occurring; especially in cases where another set of data modification instructions, dependent on the ones which are waiting for the reporting to finish has already been applied. Therefore, reporting can cause performance degradation in a production environment. So, how can reporting be optimized to prevent such a heavy load on production databases?
April 4, 2013SQL Formatting standards – Capitalization, Indentation, Comments, Parenthesis
Nobody likes to read a wall of text, even when it’s just plain text. When it comes to reading code, the problem is even bigger. Code can have different formatting styles, which could make a job either easier or more difficult. It can make code difficult to decipher and understand. A clean and neat SQL is read faster than an inconsistently written SQL; SQL reviewing and troubleshooting is more efficient; joint development efforts are more effective; handing off projects from one team to another is smoother.
April 4, 2013SQL code refactoring – ways to improve database performance (part III) – Add surrogate key
Determining just the right primary key for tables is one of the most important parts of a robust, high-quality database design. The key candidates and the keys themselves need to be picked with caution, as suboptimal choices can snowball out of control and leave the bloated, slow databases which require heavy maintenance and require massive amounts of work to meet changes in the business requirements. Therefore, due to the importance of the primary keys for the future behavior of the database, their impact on the database performance needs to be weighted as well. So, from a performance standpoint, should replacing complex natural keys with a surrogate key be considered?
April 4, 2013SQL code refactoring – ways to improve database performance (part II) – Split table
In most cases, splitting a table into two tables containing different columns is required in order to address database design changes, business requirements or even adding domain restrictions retroactively (for example, isolating currencies in a separate table and enforcing referential integrity via a foreign key to ensure that only valid currencies can be stored in the database). However, splitting tables may actually offer additional performance benefits to the database.
April 4, 2013SQL code refactoring – ways to improve database performance (part I) – Encapsulate code as a stored procedure
Database performance is a challenge for every developer and DBA. Even when some improvements are made, there is always a question – is there anything else that can be done.
There might be. Some of the performance improvement techniques are not code related – a proper indexing strategy, adding memory, using different disks for data files, log files, and database backups, using faster disks, optimizing tempdb performance.
April 4, 2013Changing SQL stored procedures and functions parameters without breaking dependencies
Changing the parameters of a SQL function or a stored procedure is easy – simply ALTER the function or the procedure and add or remove parameters, change the names or data types of the existing parameters and even set default parameter values. However, changing a parameter safely, in most cases, is anything but easy
April 4, 2013Get an alert when a certain record changes in SQL Server
Auditing a database is the first step towards staying updated about database changes. However, if specific, highly sensitive data needs reviewing, an immediate notification of any change is preferable.
Sending email alerts to one or more recipients is one of the most common ways of immediate notification. Sending emails for every change in a database is not advised. It will, in most cases, quickly fill your inbox, and add many unnecessary actions, which can also affect the overall application performance. Therefore, it is important to choose the changes that you want to be alerted about, carefully.
April 4, 2013Recover SQL data from a dropped table without backups
If you’ve accidentally executed a DROP statement and you have a full database backup, given that no changes occurred after the table was dropped, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the DROP SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data
April 4, 2013Recover a SQL Server database using an old backup and the current transaction log file
How to recover a SQL Server database using an old backup and the current transaction log file DBAs have various disaster recovery plans. One of the questions they have to answer before setting up a right plan is how much data can they afford to lose. Many of “accidental DBAs” become aware that making regular full database backups might not be enough, only after it’s too late.
April 4, 2013Recover data lost due to a TRUNCATE operation without backups
If you’ve accidentally executed a TRUNCATE statement and you have a full database backup, given that no changes occurred after the table was truncated, you can simply recover the data by overwriting the original database with the backup. However, if that’s not the case, either because the database objects and data have been changed after the TRUNCATE SQL statement was executed or because you don’t have a valid database backup, there is still a way to recover all of your lost data.
April 4, 2013How to secure stored procedures against SQL injection
Although SQL Server’s stored procedures help with code security by hiding the implementation of the business logic and even protecting against some kinds of SQL injection attacks — primarily those that use an operator such as AND or OR to append commands onto a valid input parameter value, simply wrapping the code into a stored procedure doesn’t mean that applications, database and SQL Server are safe from all types of SQL injection attacks. So, how to make stored procedures bulletproofed against SQL injections?
April 4, 2013Audit SQL Server database schema changes
Keeping track of the changes made to your database objects is a key part of any SQL database security strategy or compliance policy including, among others, the Health Insurance Portability and Accountability Act, Sarbanes-Oxley, Payment Card Industry Data Security Standard or the European Union Data Protection Directive. However, even if your IT environment doesn’t have to comply to stringent security rules, being able to identify what database object has been changed, who has changed it as well as the exact time of the change, is invaluable in troubleshooting any schema related problems down the road, such as broken dependencies. So, how does one audit SQL Server schema changes?
April 4, 2013How to build a SQL database from source control without dependency problems
One of the caveats of having your SQL database under a source control system, is the overhead when the time comes to deploy a new database build. Even if a single copy of the database isn’t shared among the developers, but rather each developer has its own local copy of the database objects’ scripts which are synchronized with source control on a regular basis, building a deployment SQL script may prove to be a rather challenging task.
April 4, 2013How to migrate a SQL Server database to a newer version of SQL Server
DBAs are well aware that downgrading a SQL Server database cannot be done out of the box. Even when the compatibility level of the database that needs to be migrated to an older SQL Server version matches that version, the backup can’t be simply restored. Upgrading can also be a problem.
April 4, 2013Audit SQL Server database and see who deleted a column value
Every DBA and developer strives to be in control of SQL Servers, databases and data. But, sometimes the situation can get out of control and unusual things start happening.
For example, you have noticed that a value from a specific table column in your database has been disappearing. You’ve checked your code as much as possible and didn’t find anything that deletes the column value, the users say they are not deleting it, you’ve checked the permissions on this table/column and found that no unauthorized deletes were allowed. But something is obviously wrong.
April 4, 2013Improve the performance of your SQL queries by qualifying object names
In addition to replacing the * wildcard in SELECT statements with an explicit list of column names, fully qualifying all SQL object’s names in SQL queries will boost their performance.
April 4, 2013How can I take nightly snapshots of SQL server database schema without having to use version control?
A developer aware of the importance of managing database changes knows that database versioning is a must. Having a database versioned in source control enables to recreate the database with the same structure it had at a certain point in the past.
The most common solution for database versioning is using a source control system. But it’s not the only possible solution. Database structure versions can be created even without having a source control system.
April 4, 2013How to verify SQL database backups
When disaster strikes, the only thing more frustrating than not having an up to date and relevant database backup is having a corrupt backup. When you first create a backup file, it should be good, with ‘should’ being the operative word. Every time the file is copied to another location, there is a risk of file corruption. A foolproof way to ensure that the file is still usable is to restore the backup itself, and run DBCC CHECKDB immediately against the newly restored SQL database.
April 4, 2013How to create a SQL build script from the source control repository
It’s quite common for developer teams to use database object versioning. The creation scripts for every table, view, stored procedure, and other objects in the database are added to a source control system. That way, everything is versioned and the team is safe.
Applying a specific version of a source control system to a database is not a problem and it can be done by using a source control client.
April 4, 2013How to restore a SQL Server database backup to an older version of SQL Server
If there was need to restore a SQL Server database backup to an older version of SQL Server there was “greeting” with error messages along the lines of:
Msg 3169, Level 16, State 1, Line 1
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
or
Msg 3241, Level 16, State 7, Line 1
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
and the attempted backup restore would fail.
April 4, 2013
