Feeds:
Posts
Comments

Archive for the ‘Operating System’ Category

Interesting one today:

Earlier this month, during Sql Server Cluster set up on a new set of VMs, ran into this interesting warning message.

Network binding Order generated a warning.

The domain network is not the first bound network. This will cause domain operations to run slowly and can cause timeouts that result in failures. Use the Windows network advanced configurations to change the binding order.
Cluster Setup Network Binding Order

Cluster Setup Network Binding Order

Upon further investigation, it became clear that the NIC that connects to the Domain network is not given highest priority (as needed) for Sql Cluster.

Resolution

In Clustered environments, it is recommended to have the network interfaces properly ordered for maximum efficiency.

Go to “Network connections” and open Advanced Settings. See the image below:

Network Connection - Advanced Settings

Network Connection – Advanced Settings

In the resultant window, under Adapters and Bindings tab, make sure the network interfaces are ordered according to the recommendation. Domain network needs to be on the top, then Heartbeat Network and Remote Access Connections. See the image below, for the recommended order.

Network Binding Proper Order

Network Binding Proper Order

After saving the new order, go back to “Install Failover Cluster Rules” and re-run the checks. This blog has more info, if interested about the rest of cluster set up.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Every now and then, when good Sql Server resources are available, they are posted here for more people to benefit from them. Continuing on that tradition, today we have a gold mine. Microsoft has released many, many & many e-books open to public to download for free. In the list there are several Sql Server books along with BI, Windows, Office, SharePoint, etc

Happy learning !!

 

Hope this helps,
_Sqltimes

Read Full Post »

Interesting one today:

A few months ago, we saw a point on CHECKPOINT and its counterparts. Now, lets dig a little deeper into it.

Starting Sql Server 2012, we have a new advanced option called TARGET_RECOVERY_TIME; It helps with setting Indirect Checkpoints to change the recovery time after a crash.

Automatic CheckPoints are the default (system level setting in sys.configurations) settings that decide how frequently the dirty pages in buffer pool are written to disk. Usually it is once every minute (generalization), but there are some nunaces to it (see NOTE below).   This helps in reducing the amount of time it takes to bring the system back to working after a crash.

Note:1 minute is just a general guideline, the actual interval depends on the amount of traffic on the database system. For higher traffic systems, there will be a lot of transactions each second, so there will be more dirty pages. So, the CHECKPOINT (background writer) kicks off more frequently than once a minute.

With new Indirect Checkpoints, a database level setting, we could configure a custom checkpoint settings to enable faster & predictable recovery times after crash.

TARGET_Recovery_Time_Setting

Context:

When we UPDATE/INSERT data into Sql Server, it is written to buffer pool, not disk. Only when (automatic/default) CHECKPOINT occurs, all the dirty pages in buffer pool are written to disk. This occurs at one minute intervals (varies based on workload, but 1 min in a good general guideline). So, approximately every minute, at the 60th second, you’ll see a HUGE spike in I/O to the MDF/NDF files as all the dirty pages are being written to disk. Then it waits for another ~60 seconds for the next CHECKPOINT, to write all the dirty pages to disk again. So, you see a pattern here.

The entire dirty page workload is being written to disk in one shot —  then wait (sit idle) for the next 60 seconds; And then again write the next workload to disk in one shot. As you can see, the I/O subsystem will be more active during these CHECKPOINT periods than at anytime in between.

If your storage is designed to handled, let’s say, 100 MB/sec and you have 1000 MB worth of dirty pages since the last checkpoint (1 min), it might take storage subsystem more than 10 seconds to fully process the workload. This results in unnecessary spikes in I/O metrics.

See the image below, where it shows the Maximum reading on the amount of dirty pages written to disk.

Default_CheckPoint_1min_interval

This presents an incorrect picture that there is something wrong with your storage. While staying idle the remaining 50 (to 45) seconds of the minute.

--
--	Change TARGET_RECOVERY_TIME
--
ALTER DATABASE SampleDB
SET TARGET_RECOVERY_TIME = 30 SECONDS;
GO

Advantages

  • I/O bottlenecks:
    • Now, if we could write more frequently, then the same workload could be accomplished without triggering off any false positive metrics (and also reducing recovery time after a crash).
    • In our above example, if the same 1000 MB dirty page workload per minute, could be written 2 times within a minute, we’ll have ~500MB workload every 30 seconds.
    • Now, the same storage metrics will show much better picture.
    • Then we could tweak the design of storage to the requirements of Sql Server dirty page workload.

TARGET_Recovery_Time_30seconds

  • Indirect Checkpoints enable you to control recovery time after a crash to fit within your business requirements

Disadvantages:

  • For OLTP workloads, sometimes this setting could result in performance degradation. Looks like the background writer, that writes dirty pages to disk, increases total write workload for server instance.
    • If different databases have different settings, the instance ends up doing more work, which might result in performance degradation.
    • So, this setting needs to be tested in performance environment before enabling it in Production environments.
In the next post, we’ll see the interaction between ‘recovery interval‘ & TARGET_RECOVERY_TIME setting.
Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

A few days ago, as part of installing Sql Server 2016, we needed to install R Service on a lab machine. During installation, we learnt that the Sql Server DVD/media does not have necessary CAB files to perform installation. We ran into this situation:

Setup was unable to contact the download server. 
Provide the location of the Microsoft R Open and Microsoft R Server 
installation files and click 'Next'. 
The installation files can be downloaded from:

https://go.microsoft.com/fwlink/?LinkId=824879&lcid=1033
 https://go.microsoft.com/fwlink/?LinkId=824881&lcid=1033

Sql2016_R_Install

Resolution:

Since our lab machines do not have internet access, these CAB files could not be downloaded by the install wizard. Using the URL’s, we could download the necessary install CAB files (2 files) to a local laptop and then copy the files over to the lab server.

Then point the install wizard to this location (local to the server) where both the CAB files are present. See image below:

Sql2016_R_Install_with_CAB_Files

 

Once the CAB files path is identified, rest of the installation progresses smoothly. See image below with R Service installed.

Sql2016_R_Install_Successful

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Recently, we ran into this interesting error when running some random queries on a Sql Server machine.

An error occurred while executing batch. Error message is: 
There is not enough space on the disk.

The error message seems nebulous and its hard to decipher what it is referring to. The usual suspects of MDF & LDF files are all good. All data and backup drives on the server have plenty of free space. So it was a bit confusing.

Upon further banging-head-on-the-desk, it became obvious that the problem is not with Sql Server, but with SSMS. On Operating System, Management Studio has its own workspace, where it stores all the results, and that is running out of free space.

Resolution:

  • Go to %TEMP% directory and clear up some space.
  • And clear up some more space on C: (where SSMS is installed).

With these two actions, SSMS works again.

Hope this helps,
_Sqltimes

Read Full Post »

Quick one today:

Earlier today, when installing Sql Server 2016, ran into this interesting error:

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

oraclejre_error

Looks like JRE (Java Runtime Engine) is needed for Polybase application to run. So, lets get it from Oracle website here. Download from the JRE section (see image below).

oraclejre_error2

In the new page, “Accept License Agreement” & download the version pertinent to your environment.

oraclejre_error3

After installing JRE, the checks ran successfully for install.

Hope this helps,
_Sqltimes

Read Full Post »

Older Posts »