SQL Server Help

26/05/2025

SQL Server Performance Tuning
This is part of a series of posts on performance tuning, which I'll be creating during May and June 2025.

SQL Server Performance tuning is hard.

Index Management: Effective indexing is crucial for high performance. We excel in designing and maintaining optimal indexing strategies, leading to substantial performance gains.

Query Optimization: Businesses often face challenges with slow query performance. Our expertise in fine-tuning complex queries and optimizing ex*****on plans ensures significant improvements in application performance and efficiency.

Database Design: Addressing poor database design and schema issues is essential for enhanced performance and scalability. Our proficiency in database architecture dramatically improves these aspects.

We can help you to:

- Use Extended Events to gain deeper insights into query behavior.

- Use techniques for identifying and resolving query blocking issues.

- Understand ex*****on plans and learn how to extract valuable information from them to optimize query performance.

- Gain a clear understanding of statistics and how to use them effectively to enhance performance.

- Use modern indexing strategies, to significantly accelerate query ex*****on.

- Learn about the causes of query recompilation and strategies for managing it when it impacts performance.

- Use the Query Store to identify underperforming queries and apply targeted fixes.

- Build your understanding of how the query optimizer can automatically improve query performance.

- Develop and apply a proven query tuning methodology to consistently improve the speed and efficiency of your queries.

12/08/2024

Why use us for Data Warehousing and Data Engineering?

We have implemented many data warehouses in SQL Server, using SSIS and SQL stored procedures for ETL / ELT.

We're highly experienced and proficient in T-SQL, stored procedures, triggers, SQL troubleshooting , and using SSIS to create fast, accurate and secure data pipelines.

More recently, we have been involved in Microsoft Power BI and DAX to create compelling reports and dashboards for all sizes of business. As part of this, we have used SSRS to create paginated detail reports.

Other roles include presenting, providing training, and passing on SQL Server / Power BI and DAX skills to other team members.

Our Data Warehousing Experience

For ETL/ ELT, we have written many, many SSIS and SQL scripts, including advanced data transformations, database change tracking, slowly changing dimensions (SCDs) custom scripts in VB and C #, connecting to disparate OLEDB and ODBC data sources, error tracking and deployment.

What are the Challenges when designing and implementing a Data Warehouse?

Designing a data warehouse requires a solid understanding of the organization's data requirements, as well as the data sources that are available. The following are the key considerations in designing a data warehouse:

Data Sources
Determine the types of data sources that need to be included in the data warehouse, including transactional systems, legacy systems, and cloud-based systems. The data sources need to be integrated into the data warehouse to ensure that the data is consistent, accurate, and up-to-date.

Data Modeling
Define the data structure for the data warehouse, including the relationships between tables, data elements, and attributes. The data model should also take into account the organization's data requirements, including the types of reports and analysis that will be performed.

ETL (Extract, Transform, Load) or ELT (Extract, Load, Transform)
Develop an ETL or ELT process to extract data from the sources, transform the data into a format that is suitable for the data warehouse, and load the data into the data warehouse. This process should be automated and regularly scheduled to ensure that the data in the data warehouse is up-to-date.

Data Quality
Ensure the data quality of the data in the data warehouse by implementing data validation and data cleansing rules. This helps to ensure that the data is accurate and consistent, and minimizes the risk of incorrect data being used for analysis and reporting.

Performance
Ensure that the data warehouse is optimized for performance, including the indexing of data, the use of summary tables, and the use of materialized views. This helps to ensure that the data can be retrieved quickly and efficiently for analysis and reporting.

Security
Implement security measures to ensure that the data in the data warehouse is protected from unauthorized access, modification, and deletion. This includes access controls, data encryption, and audit trails.

Scalability
Ensure that the data warehouse can scale to accommodate growing data volumes and increased demand for analysis and reporting. This includes the use of distributed systems, data partitioning, and load balancing.

Maintenance
Develop a maintenance plan for the data warehouse, including regular backups, data archiving, and disaster recovery planning. This helps to ensure that the data warehouse is available and reliable for analysis and reporting.

10/08/2024

Recovering an MSDB database
Recovering an MSDB database in SQL Server marked as ‘suspect’.

Recently, after the server reboot, we got a message that MSDB was in a suspect state. Panic ensued. We had to repair it.! We thought it would be fairly straightforward to fix., but it was actually more complex than we thought.

Attempt 1.: Restore from a backup.

Well, our first thought was to simply restore the database from last night’s backup. Right? Wrong! We logged a ticket with the server team, asking for the backup to be made available. However, it would seem that there was no backup for this server. (it’s only a dev server). So we needed another solution. Even though it was only a dev server, it contained many months of work, with new tables, stored procedures, and triggers existing only within it and nowhere else.

Attempt 2: Copy another SQL Server MSDB backup.

Our next thought was to restore a backup from a similar instance in SQL Server with the same version and build number. This solution will of course result in data loss. You also have to restore from T-SQL, rather than from SQL Server Management Studio GUI!

Unfortunately, we couldn’t find a backup that matched the dev server’s version and build number, so this solution was not possible either. We needed to try something else!

Attempt 3. Use a SQL Server database Template.

Fortunately, there is another solution. When you install SQL Server, the install process creates a template for each of the system databases. For our version of SQL Server (2022), the templates are stored in C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\Templates

We were able to shut down the instance of SQL Server, copy the template, and restart. The SQL Server instance started, without any warnings or errors.
At this point, we still had to recreate things like jobs and mail configuration, as we didn’t have any backups of those as SQL scripts.
In conclusion, I guess that the point is of all this is that you need backups, and a proper disaster recovery procedure even just on a dev server. Because you never know when you might just save your skin.

29/07/2023

New Sample Power BI dashboard :

https://www.sqlhelp.co.uk/sample-power-bi-page/

Uses bookmarks, selections, drill-throughs and advanced DAX. The data comes from a SQL Server data warehouse., populated using stored procedures.

For more information about how we created it, get in touch by DM.

23/07/2022

https://www.sqlhelp.co.uk/do-i-need-a-data-warehouse-for-power-ni/

If you have been using Power BI for a while now, you may have heard that you should have a data warehouse. But do you really need one>

28/06/2022

My old faithful (and dog eared and coffee stained) DAX book. Still my go-to book after all this time. Highly recommended.

28/09/2021

Struggling to get started with Power BI and Data Warehousing ? Try our free 30 minute consultancy session. No obligation. https://sqlhelp.co.uk/contact-us