SQL Bits 2017 Sessions List Up For Voting!

Those lovely peeps at SQLbits have put the sessions list up for you even more loevly people to vote on who you would like to waffle on at you for your chosen sessions!
http://sqlbits.com/information/publicsessions

I have submitted a talk with David Postlethwaite which is on this mighty list. So if you fancylearning more about SQL Server Management Studio 2016 features, shortcuts and tid bits to help with your DBAness go vote for us!
http://sqlbits.com/Sessions/Event16/A_real_DBA_don_t_need_a_GUI_-A_Guided_Tour_of_SSMS


Belrb from SQL Bits on why you should attend

Why should you attend

Where else can you spend a whole day being taught by the very best in the industry at such a modest cost.

You'll learn more in 4 days than you did in the last year. You might well win something great in one of the many prize draws they run. You'll certainly meet lots of friendly techy types.

I have been to SQLBits for the last few years and the knowledge that I have gained has put me above colleagues in terms of expertise that I may not have picked up elsewhere.

An unbeatable combination of really great value training, the ability to pick and choose the sessions which interest you and the opportunity to mix with your peers. It's the highlight of the year for SQL DBAs

Love every single bits. Cheapest price ever for training and you get world class speakers!

The best SQL Server conference in Europe. Unbelievable know-how in a pleasant atmosphere.

I do concur, great sessions, people are fun and happy to share their massive welath of knowledge. The networking parties are usually fun and with the bonus you have the option of dressing up for their event theme parties! Last year was space, year before was super heroes and the year before that steampunk....

Go register ..... no really.... go go....

Weblogic Fun Times - Part 1

So part of my BAU work I dabble in the world of Oracle Weblogic Server .... like a little bit... like dip my toe in now and again...

Anyway I thought I would share some high level bite size info with you all for giggles :)

So weblogic, is the middle tier that can sit between a back-end database e.g. oracle and  services front end web applications and web services.

Here is a nice picture with stick people to show who looks after to which bit normally. 

Pic Source

Oracle official definition: A WebLogic Server administration domain is a logically related group of WebLogic Server resources.

When you install your domain what you will have first of all is your Admin Server, this will manage your domain config.

You can administer the weblogic via its web interface known as the console. Usually a url something like http://MyDomain:7001/console/

you can also administer directly on your host server. A lot of occasions this may be a Unix/Linux platform.

You will also have an instance of the weblogic server which is your Managed Server. This is where we would deploy the applications to.

You can also utilise a weblogic cluster to provide high availability and scalability for your applications.

Weblogic also uses a utility called NodeManager that enables you to start, shut down, and restart Administration Server and Managed Server instances. As part of the start/stop process it does crash recovery checks on your Admin and managed servers to make sure we can start them backup up cleanly.

Weblogic has its own scripting tool known as WLST (WebLogic Scripting Tool) which you can script out and automate features related to the setup and configuration of your domain. 

Pic2 Source

So that is our high level components in bite sized chunks.....coming up we may delve into install and high level checks.

Catch ya later
Little Maz

MS SQL 2016 just got a whole lot cooler....

MS SQL 2016 SP1 has arrived! Hurrah I hear you say...

https://blogs.msdn.microsoft.com/sqlreleaseservices/sql-server-2016-service-pack-1-sp1-released/

Those nice people at Microsoft have given the standard edition a whole load of extra benefits which previously only on the enterprise edition of MS SQL server.

The following table compares the list of features which were only available in Enterprise edition which are now enabled in Standard, Web, Express, and LocalDB editions with SQL Server 2016 SP1

Think my fav new addition is the Database clone command! Troubleshoot our production databases by cloning the schema and metadata, statistics without the data.

 –– Default CLONE WITH SCHEMA, STATISTICS and QUERYSTORE metadata.
DBCC CLONEDATABASE  (source_database_name, target_database_name) 

–– SCHEMA AND QUERY STORE ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS                                 


–– SCHEMA AND STATISTICS ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_QUERYSTORE 


 –– SCHEMA ONLY CLONE
DBCC CLONEDATABASE  (source_database_name, target_database_name) WITH NO_STATISTICS,NO_QUERYSTORE   

Download SP1

https://www.microsoft.com/en-us/download/details.aspx?id=54276


Catch ya soon!
Little Maz

More with SQL Server on Linux

Loving the channel 9 video for MS SQL on Linux

Performance tweak thoughts...Optimize for Ad Hoc Workloads

So I noticed in my current environment (enterprise insurance company) we aren't really utilising this option as a performance tweak. To be honest I do turn this feature on for most of my builds, so far I haven't seen any major detrimental effects. I would say that this option is 100% for every instance and application profiles but is has done me ok so far...

An ad hoc workload is essentially a query not put into a stored procedure so the full T-SQL Query is run each time.


Introduced in MS SQL 2008; with the instance option “Optimize for Ad Hoc Workloads” (bah use of z!) enabled, SQL Server will create a small stub in the plan cache when the query is run for the first time. When that query is run for the 2nd time, then it will create a fully compiled plan, showing that ‘hey this query is valid’ and not a one off.


This will relieve memory pressure by not allowing the plan cache to become filled up with compiled ‘junk’ plans that are not likely to be reused.

-- clear the cache for fresh results
DBCC FreeProcCacheGO 

-- run a new query
SELECT [ID],[Procedure_Name],[Description],[Run_Date]
FROM [monitoring].[dbo].[MyLogTable] WHERE [ID] = 5045
GO

-- check to see the plan cache allocations/sizes
SELECT usecounts,cacheobjtype,objtype,size_in_bytes,[text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 









This shows the Plan Size for this query: 16384 bytes.


We can turn on the option for Ad-hoc workloads optimisation via the following below T-SQL commands or in SSMS at MS SQL Instance level right Click >Properties > Advanced > Change 'Optimize for ad hoc workloads' to TRUE.

-- show advanced optionssp_configure 'show advanced options', 1
GO
RECONFIGURE
GO

-- enable optimize ad-hoc workloads
sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO


With this now turned on we can clear the cache and run this again to show the benefit ofthe reduce memory allocation as a stub in the plan cache.








This shows the plan size this time as 128 bytes, which is a big difference from our previous run! This time only the stub is stored in the cache, not the entire plan, so we don’t waste memory with a query that may never be executed again.

Why is this Good?
Memory allocated for execution plans comes from our buffer pool, so the more plans we have, the smaller the buffer pool will be for data and index pages. With this option enabled, we can save our cache from becoming bloated.


Sounds Great! Whats the Downside?!
Also it could affect your performance in an environment where you have a huge plan cache, this feature would add to your wait time. Whenever a new query comes in, SQL Server has to check to see if that plan has ever been seen before. This lookup operation isn’t free along with inserting a plan or plan stub into the cache. So if you do enable... TEST, TEST, TEST and er TEST some more!

So We Wouldn't Enable this when....
... our system runs every single query twice. Then there would be no point to this option being enabled. No benefit/gains to release additional resource to the memory pool. Not aware of many apps/developers with this type of execution profile! The side-effect is that you introduce a small CPU overhead initially, because every execution plan must be compiled 2 times before the plan is finally stored in the Plan Cache.


Evidence!
We like to show our boss our memory efficiency savings! A good script I came across on the Interweb which works from MS SQL 2008+

-- for 2008 and up .. Optimize ad-hoc for workload 
IF EXISTS (
         SELECT 1
         FROM sys.configurations
         WHERE NAME = 'optimize for ad hoc workloads'
        )
 BEGIN
      DECLARE @AdHocSizeInMB DECIMAL(14, 2)
         ,@TotalSizeInMB DECIMAL(14, 2)
          ,@ObjType NVARCHAR(34)
       SELECT @AdHocSizeInMB = SUM(CAST((
                     
   CASE                        
    WHEN usecounts = 1 AND LOWER(objtype) = 'adhoc' THEN size_in_bytes
    ELSE 0
   END
   ) AS DECIMAL(14, 2))) / 1048576
   ,@TotalSizeInMB = SUM(CAST(size_in_bytes AS DECIMAL(14, 2))) / 1048576
  FROM sys.dm_exec_cached_plans
  SELECT 
   ' Total cache plan size (MB): '
   + CAST(@TotalSizeInMB AS VARCHAR(max)) + '. Current memory occupied by adhoc plans only used once (MB):' 
   + CAST(@AdHocSizeInMB AS VARCHAR(max)) + '.  Percentage of   total cache plan occupied by adhoc plans 
     only used once :' + cast(CAST((@AdHocSizeInMB / @TotalSizeInMB) * 100 AS DECIMAL(14, 2)) 
     AS VARCHAR(max)) + '%' + ' ' AS   COMMENTS ,' ' + CASE 
  WHEN @AdHocSizeInMB > 200
  OR ((@AdHocSizeInMB / @TotalSizeInMB) * 100) > 20 -- 200MB or > 20%              
  THEN 'Switch on Optimize for ad hoc workloads as it will make a significant difference.'
  ELSE 'Setting Optimize for ad hoc workloads will make not a large amount of difference.'
 END + ' ' AS RECOMMENDATIONS
END

So generally I do like to turn this on, but would say test things and don't put it straight onto your prod boxes! Hopefully that does go without saying..... ;)

See below some additional reading........Happy Memory optimising! 

• http://www.sqlskills.com/blogs/kimberly/plan-cache-and-optimizing-for-adhoc-workloads/
• http://sqlblog.com/blogs/kalen_delaney/archive/2013/06/25/settings-to-always-change.aspx
• https://bradmcgehee.com/2011/04/25/do-you-enable-optimize-for-ad-hoc-workloads/
• http://www.sqlskills.com/blogs/bobb/performance-features-in-sql-server-2008-rc0-optimize-for-adhoc-workloads/
• https://blogs.technet.microsoft.com/josebda/2009/03/19/sql-server-2008-optimize-for-ad-hoc-workloads/
• https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change/
• https://msdn.microsoft.com/en-us/library/cc645587.aspx

First Usergroup Talk!

So I was volunteered by my colleague David Postlethwaite to talk with him at our local MS SQL Usergroup in Southampton.

Never been a hug fan of public speaking but figure I was amongst fellow geeks so how bad could it be? plus free pizza.... mm pizza...

So our topic as a nice ease into a speaker topic was 'A Guided Walkthrough of SQL Server Management Studio'. 

So from the basics of what is a query window, Object Explorer to some of the nice little features and shortcuts that have been introduced in 2016.

So BIG Thanks to SQL Southampton usergroup for letting David and myself waffle on at the attendees! 

HUGE Thanks to David for letting me steal some of his speaking time to let me dip my little toe into the world of MS SQL Usergroup speaking.

Hopefully peeps learnt some little SSMS tid bits they didn't know previously! 
I will post the powerpoint to this blog so you guys can check it out :)

Cheers,

Little Maz

So I finally Decided to do this blog thing....

So I finally Decided to do this blog thing....

Yup after much nagging from chums and colleges I decided to write some of my geek, DBA musings and experiences down....

So let's see.....I have been a DBA for about 6 years now. Mainly experience with Windows / MS SQL Server Environments from MS SQL 200 up to new and shiny MS SQL 2016.

My current job I have just finished 6 months and delved into the dark side of Oracle Database & Middleware fun times, so may post about my new experiences on this too.... at the moment is like voodoo... when you are picking through your forms printer config post script for static printer tray entries you wonder... this is SQL!? But hey interesting times ahead...!

I have worked in IT for about 12 years.... Worked in large companies, small companies, public and prviate sector... not bad considering I am 30... er something.....

I studied IT at university, yes I actually wanted to work in IT.... people usually give me odd looks when I say that. 

I was a secret geek for much of my younger life. Playing my ZX Spectrum 48k in my bedroom like a good little geek gal! Traded up through the various home pc incarnations 286, 386, 486, 586, pentium (woo!)... and fast forward to today I run a decentish PC gaming rig and looking to upgrade my GPU to a Nvidia 1080 with a 4k monitor to match... I saw this video of Doom in 4k and I was sold....! ;)


So... I will post mainly DBA stuff..... some other geeky type stuff that I like..... and more than likely star wars stuff..... 


Cheers Peeps,
Little Maz