MrDatabaseAdministrator

  SQL server pretty much depends upon the statistics it collects  for the data objects, Statistics of a data object has major say  in the way sql designs plans and handles execution This gives us the loop hole in tricking the relational  engine/Query processor into designing query plan depending  upon the statistics which we alter. Sql server allows updating statistics manually, in this blog we  are going to trick relational engine into thinking a newly  created table and its effect on execution plans. UPDATE STATISTICS <table_name> WITH ROWCOUNT=50000, PAGECOUNT=24000; the above query of  UPDATE STATISTICS using option of  ROWCOUNT and PAGECOUNT enables us to manually set the  row count of table without having actual rows inserted, the  optimizer will not just look for the rows but also the amount  of pages that it needs to read to get the data hence the PAGECOUNT  lets implement it to a basic table : The stati...

Usually the goal of performance tuning is to make your query run faster.  One of the easiest ways to get a faster query is to reduce the volume of data a query is processing.  STATISTICS IO makes it easy to see how much data SQL Server is actually processing. How it is going to help?  The data it shows acts as a measuring for your performance tuning.  It provides a good way of knowing the number of io Sql server has gone to fetch the data  It is session specific only How to Use? -- For current session only SET STATISTICS IO ON; GO How to read the output?  Logical reads: The number of pages SQL Server had to read from the memory in order to process and return the results of your query.  Query performs slower if number of pages read is high.   Physical reads:  this read happens when the page requested is not present in the cache and needs to be fetched from files on the disk, this operation causes a IO wait.   Worktables/Workfiles:  sql server cre...

Linked servers always have a surprise error hidden under its selvee, Managing these issue magnets is a huge task for a DBA , recently i was stumbled upon a same suprise invloving a oracle linked server. The linked server "oracle_db" is present on my prod db server essentially for select and insert purposes, Developers tested the linked server with couple of select querries and all was fine until the deployed a procedure on server and executing the same gave a alien error OLE DB provider "OraOLEDB.Oracle" for linked server "oracle_db" returned message "ORA-01403: no data found" inspecting the code it was seen that the table from linkedserver was directly involved in a " inner join" and other joins ,re running the same satement produced the given error . The code snippet was pinned, diagnosis and resolution was underway After a thorogh R&D it was found that either taking the whole oracle side output into a #Temp table and then using t...

With rising complexity in applications, organizations are turning to heterogeneous database platforms for scalability, efficiency and cost effectiveness. This leads the database administrators to create connectivity between databases of two different platforms. SQL server has utility of linked server which helps tremendously when it comes to configuring such inter database connectivity. MariaDB based on Linux lacks this feature but surely provides a work around for the same. Here's when table with CONNECT table type comes into picture CONNECT  uses this API to access data contained in other DBMS without having to implement a specific application for each one. It works with ODBC drivers provided by Microsoft (freeware), with drivers in place MariaDB can now connect to SQL server using the installed driver with a connection string Microsoft® ODBC Driver 17 for SQL Server® - Windows, Linux create table test_student ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION= 'Driver=/opt/mi...