Articles tagged Database Administration

15 June 2021
15 June 2021

Microsoft SQL Server to Oracle Database Connectivity from SSIS using Attunity Connector

0
11
Almost every environment now a days need to source data from different source system. Here is how you can establish connection using Attunity Connector from Microsoft SQL Server to Oracle Database  from SSIS. This method is extremely fast if you want to land millions and millions of records on the fly from Oracle to SQL Server. … Read more
26 February 2019
26 February 2019

Identifying Page Information in SQL Server 2019

0
3
SQL Server 2019 has some new and very interesting functions to identify information about pages. Many operations, such as analyzing current activities, locks or deadlocks, may result in some metadata pointing/blaming one page in the database. However, how to translate this information to get a database and object name? The image below is an example of … Read more
12 January 2019
12 January 2019

A real parameterization problem with a plus

0
1
A few weeks ago I faced this problem: One query on my application was (fortunately in the development environment) was facing a very bad execution time. Since the query was generated by entity framework, I used SQL Profiler to capture the query with all its parameters and execute in SSMS. The query was created using … Read more
08 December 2018
08 December 2018

Graph Edge Constraints and a Crystal Ball

0
2
When I read the list of new features in SQL Server 2019 I became very proud of my crystal ball powers. In July 2017 I published an article about Graph Database feature in SQL Server 2017. In this article, besides showing the improvements and benefits I also highlighted one problem: the lack of graph edge … Read more
28 November 2018
28 November 2018

New Technologies to Study

0
8
One great result from PASS Summit, especially when we are close to a new SQL Server release, is to identify important technologies to study on the following year. PASS Summit 2018 was great, with sessions about many new technologies giving us very good guidance on where to focus our study for the new year. Let’s … Read more
07 December 2017
07 December 2017

SQL Server Internals: In-Memory OLTP

In this book Kalen Delaney introduces and explains how the 2016 In-Memory OLTP engine works. The Hekaton internals knowledge offered in this book will help you migrate existing tables or databases to Hekaton, and get faster performance from your SQL Server applications than you ever thought possible.… Read more
16 March 2016
16 March 2016

The SQL Server 2016 Query Store: Forcing Execution Plans using the Query Store

The SQL Server 2016 Query Store can give you valuable performance insights by providing several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. However, it can also directly affect the performance of queries by forcing Execution Plans for specific queries.… Read more
07 March 2016
07 March 2016

The PoSh DBA: Assigning Data to Variables Via PowerShell Common Parameters

Sometimes, it is the small improvements in a language that can make a real difference. PowerShell is able to introduce extra common parameters that can be used by any Cmdlet or advanced function. When -PipelineVariable was introduced in V4, we all wondered what it could be used for. Laerte experimented and discovered that it could be a real convenience in every-day scripting with the pipeline.… Read more
11 February 2016
11 February 2016

Documenting SQL Server with PowerShell

SQL Server instances are generally poorly-documented. How easily can you tell if something has changed? How easily can you check that there is adequate space for growth? Are you up-to-date with licenses? What errors are happening? Who has accessing the system? Before PowerShell, it was difficult to be on top of all this. Now you can, with the help of Sander's database documenter.… Read more
08 February 2016
08 February 2016

Five Online Database Modelling Services

To design, or redesign, a database of any complexity, the Entity-Relationship modelling tool becomes essential. The specialized tools that have dominated the industry for a long while are expensive and are installed on a workstation. Now that browser technology has progressed so rapidly, the online database modelling tools have become viable and are starting to attract the attention of database designers. Are they good enough to use now? Robert Sheldon finds out.… Read more
26 January 2016
26 January 2016

The SQL Server 2016 Query Store: Accessing Query Store Information Using DMVs

The SQL Server 2016 Query Store provides several new ways of troubleshooting queries, studying their plans, exploring their context settings, and checking their performance metrics. In using the Query Store to ensure that performance is as good as possible, it isn't long before it becomes important to be familiar with the DMVs that are associated with the query store, and using them in custom queries.… Read more
06 January 2016
06 January 2016

The SQL Server 2016 Query Store: Built-in Reporting

One of the most important features of the SQL Server 2016's new Query Store is the reporting. With these features, it is now possible to get a wealth of information on how your query workload is performing, either aggregated for the entire query workload or for a single query. With this information, you can see the effects of 'forcing' an execution plan for specific queries and get feedback of the consequences.… Read more
04 December 2015
04 December 2015

The PoSh DBA – Using the Basic PowerShell Paradigms

PowerShell is like any computer language: you must understand the paradigms, the constructs, and the way it is designed to work to get the most value from it. It is no good just translating 'sausage-string' procedural algorithms. To demonstrate how PowerShell should be used as its' creators intended, Laerte Junior shows the difference between PowerShell problem-solving with, and without, PowerShell paradigms.… Read more
16 November 2015
16 November 2015

Doing a SQL Server Healthcheck via PowerShell

PowerShell is an ideal tool for doing health checks of a collection of SQL Server instances, and there are several examples around, but few acknowledge the fact that individual DBAs have their own priorities for tests, and need something easily changed to suit circumstances. Omid's health check allows tests to be SQL or PowerShell and requires only adding, altering or deleting files in directories. … Read more
16 November 2015
16 November 2015

The SQL Server 2016 Query Store: Overview and Architecture

SQL Server's Query Store, introduced in SQL Server 2016, helps to troubleshoot query performance by capturing a range of information about query usage, CPU, memory consumption, I/O and execution time, and retaining every Execution Plan for analysis. Much of this information is available through queries. It looks set to be the most significant enhancement of SQL Server 2016.… Read more
02 November 2015
02 November 2015

SQL Server Heaps, and Their Fragmentation

In SQL Server, heaps are rightly treated with suspicion. Although there are rare cases where they perform well, they are likely to be the cause of poor performance. If a table is likely to have a large number of changes, then it can become fragmented due to way that space is allocated and forward pointers used. How does one detect this problem? Is it significant? How does one deal with it, if necessary?… Read more
21 October 2015
21 October 2015

Automating Your SQL Server Best Practice Reports: The Document

If you're making a report from table-based data, an MS Word document is often a good option. In the second part of his introduction to SQL Server best-practice monitoring, Laerte Junior shows how to use PowerShell scripts to create a Word-based report with colour-coded alerts where there are problems or best practices aren't being followed.… Read more
06 October 2015
06 October 2015

Automating Your SQL Server Best Practice Reports:The Checks

A DBA in charge of a whole lot of databases and servers has to check regularly that there are no likelihood of problems. The task is well suited for automation as workload increases. Laerte Junior introduces a PowerShell-based reporting framework that aims to simply provide a Word-based report with colour-coded alerts where there are problems or best practices aren't being followed.… Read more