SQL dbo.Chris

US Market recap: The Dow Jones Industrial Average's for the week before Christmas 2018 is a staggering 6.7 percent loss, putting it on track for its worst week since the financial crisis in 2008. The Nasdaq Composite Index fell into a bear market. The Federal Reserve's rate hike on Wednesday drove the losses this week and fears of an extended government shutdown only added to the pain on Friday The Nasdaq lost 8.5 percent on the week and is now 22 percent below its record reached in August 2018. The S&P 500 lost 7 percent for the week and on Thursday hit a 14-month low. It's now down 18 percent from its record. The Dow also lost 7 percent and more than 1,600 points on the week. The Dow and S&P 500, which are both in corrections, are on track for their worst December performance since the Great Depression in 1931, down more than 12 percent each this month. The Dow is on track for its worst month since October 2008. Both the Dow and the S...

I am going to show around 90% of the screens once you have downloaded an update and will be using SQLServer2012-KB4091266-x64.exe which happens be the most recent at the time of this blog post On-demand hotfix update package for SQL Server 2012 SP4. You may have your favorite patching site that you prefer for SQL update, but here is an unofficial build chart lists site that has known Service Packs (SP), Cumulative Updates (CU), patches, hotfixes and other builds of MS SQL Server 2019, 2017, 2016, 2014, 2012, 2008 R2, 2008, 2005, 2000, 7.0, 6.5 and 6.0 that have been released. Microsoft SQL Server Version List  https://sqlserverbuilds.blogspot.com/

Say you wanted to grow the transaction log of a database from a few hundred megabytes to 100GB or even 500GB on 2 striped 1.73TB NVME drives in a 4 disk pool using Storage Spaces direct.  First this is something you would NOT normally do, but if you had some some time on a new fast server these are the sort of fun crazy things you do in your spare time.  Simple math shows that the Local NVME storage was 2.2 Times faster than the SSD SAN based storage connected over 40Gb FCoE. In this example: I grew the log file 500GB on an I3 AWS Local NVME instance that took 8 Minutes and 32 Seconds In this example: I grew the log file 100GB on a Dell Compellent SC8000 with an SSD backed lun that has around 15k Write and 30K Read IOPS taking around 3 minutes and 55 Seconds

Here is a quick script to find ALL databases on a server with size and file locations and a little more.  If multiple file groups exist it will put them in a pipe delimited format in the datafile or log file locations   Example: Query Information From All Databases On A SQL Server Instance -- Get database information, file locations and sizes -- xp_fixeddrives may need to be commented out in some environments -- comment out lines as needed  IF   OBJECT_ID ( 'tempdb..#DriveInfo' )  IS   NOT   NULL   DROP   TABLE  #DriveInfo CREATE   TABLE  #DriveInfo(Drive  CHAR (1),MBFree  INT ) INSERT   INTO  #DriveInfo        EXEC   master ..xp_fixeddrives ------------------------------------------------------------- -- Drop #Tmp_tblDatabaseInfo table if it exists IF   OBJECT_ID ( 'tempdb..#Tmp_tblDatabaseInfo' )  IS   NOT   NULL   DROP ...

How to run performance counters on Windows Server 2008 R2 Today I needed to setup a configuration to mirror what someone had sent me so that I can estimate a baseline of disk performance to calculate disk IOPs for a SQL server move to a different datacenter.  This will only be a simple setup to get at the numbers I need so I can start the compare.  If I have more time I will show values and calculations in another post. 1. Open Performance Monitor by running PERFMON in the “Run…” textbox or navigating to Icon under all programs 2. Under “Reliability and Performance”, expand “Data Collector Sets”. 3. Right-click on “User Defined” 4. Select “New” from the popup menu then select “Data Collector Set”. 5. Assign a name for the new log settings. Eg.: “ServerName_Perfmon.” 6. Under the “Create data logs” section, check the checkbox for “Performance counter”. 7. Click the “Add…” button to start adding counters for each “Performance object” you wish to add counters from. Add ...

I had some time at AWS re:invent to play with these I3 instances and a few others which are designed for I/O intensive workloads and are equipped with super fast NVMe SSD storage.  These instances can deliver lots of fast read and write IOPS making them a great fit for any workload that requires high throughput and low latency including relational databases for Microsoft SQL and Oracle databases server. When compared to the I2 instances, I3 instances deliver storage that is less expensive and more dense, with the ability to deliver substantially more IOPS and more network bandwidth per CPU core.  Additional, if you take advanatage of the  I will show some of the Read and Write benchmarks using the  Diskspd Utility : A Robust Storage Testing Tool (superseding SQLIO) with DiskSpd Batch that was created by HeraFlux Technologies which can be found HERE    as well as the quick button push of CrystalDiskMark.  So if you want to see all the numbers ...