May 2011 - Posts
Every time I go to a client to do a review I find I’m looking in the event log trying to find information. Every time I feel like I’m hitting my head against a wall as the process is awful and painful.
Given SQL is a relational database production the error log makes its best to hide that fact. You can’t sort results. You can filter them but only to find stuff you can’t exclude messages.
Features I would like to see include the following
1. a means to exclude messages
2. more categorisation
3. sortable columns
4. Exclude filters not just include ones
5. Time filtering using a slide bar.
I’ve raised a connect item to see if we can get improved https://connect.microsoft.com/SQLServer/feedback/details/671840/improve-the-log-viewer-to-aid-monitoring-and-manaagement
I don’t normally post about job opportunities I see but I had to post this as its near where I was brought up.
Shropshire fire and rescue are looking to recruit a database developer. There aren’t many IT roles in Shropshire so if you are interested why not apply.
When you expand object explorer and see “Agent XPs Disabled” don’t worry. This happens when SQL Agent is stopped. I guess the idea is to reduce the surface area if SQL Agent isn’t running.
You don’t have to manually enable them. They will be enabled automatically when SQL Agent is started.
This was prompted by Paresh’s post http://paresh-sqldba.blogspot.com/2011/05/agent-xps-disabled-sql-server.html
If you use multiple email addresses and you want to use BPOS then to be able to send on behalf of those email addresses you have to set up distribution lists and then grant your account permission to send on behalf of those distribution lists.
Unfortunately the Admin UI doesn’t provide all the functionality to do that. It only enables you to create the DL.
To assign the sendAs permission you need to resort to the Migration tool box and powershell.
Download the migration tool kit from the http://admin.microsoftonline.com site
One installed you can launch the migration command line from the start menu.
This will load the cmdlets into a powershell environment. You can then run the code from the following forum post
For reference this will launch a pop up for you to enter your BPOS credentials and stores them in the $cred object for future use. If you don’t do this you will have to specify your credentials every time you run a command
$cred = Get-Credential
Once you’ve stored your credentials you can use the following
Add-MSOnlineMailPermission -Identity <distribution list email> –TrustedUser <your email> -Credential $cred -GrantSendAs $true
I hope that helps.
If you haven’t accidentally created objects in master then you can’t be developing on SQL. Invariably this happens when you have a DB creation script that has a USE <DB> at the start. If the DB doesn’t exist then you get an error but SSMS just carries on in the existing database.
This means you end up with lots of objects in the wrong database.
This script generates the drop statements based on the object type. It doesn’t actually drop them you have to copy the relevant statements and then run them in a new window. I don’t like automatically dropping objects automatically so this is a nice compromise. The query returns the date as well so you can select the objects you want to drop.
select case o.type_desc
when 'USER_TABLE' then ' DROP TABLE ' + schema_name(o.schema_id) + '.' + quotename(o.name,'[')
when 'DEFAULT_CONSTRAINT' then 'DROP DEFAULT ' + schema_name(o.schema_id) + '.' + quotename(o.name,'[')
when 'PRIMARY_KEY_CONSTRAINT' then 'ALTER TABLE ' + schema_name(p.schema_id) + '.' + p.name + ' DROP CONSTRAINT ' + quotename( o.name,'[')
when 'UNIQUE_CONSTRAINT' then 'ALTER TABLE ' + schema_name(p.schema_id) + '.' + p.name + ' DROP CONSTRAINT ' + quotename(o.name,'[')
when 'SQL_STORED_PROCEDURE' then 'DROP PROCEDURE ' + schema_name(o.schema_id) + '.' + quotename(o.name,'[')
when 'VIEW' then 'DROP VIEW ' + schema_name(o.schema_id) + '.' + quotename(o.name,'[')
from sys.objects O
left join sys.objects P on O.Parent_object_id =P.object_id
order by o.create_date desc
This is a simple placeholder post for me to be able to find the KB article about how to do this
You either need to have a KMS (Key Management Server) in place or do it manually using the slmgr vbs script.
Guy who runs DDD South West has organised and is running some speaker training next week.
For anyone that is considering or currently doing speaking you can never have enough training. You will always learn something and the more you see the better your own speaking will become.
If you are interested then drop them an email.
For a while now I’ve been struggling with the performance of my laptop. Over time it starts to run slowly videos play really badly and anything that is CPU bound just takes ages. Anyone thats seen my demos recently might have seen some erratic behaviour. I put it all down to the bloat of stuff installed on my machine. So I started to go from scratch.
I purchased an SSD to eliminate the hard disk and installed windows 7.
Everything went well and all installed from a FusionIO memory card in 10 minutes whilst on the train.
Running the windows performance measuring thing I got some good numbers 5.3, 5.3, 5.4, 5.4 and 7.8 (its a good SSD). So I thought all was good. I started to install other software such as office and some of the drivers, as the video was bad I though I might have a duff driver installed.
One thing I used to test the performance was SuperPi. This is a handy utility that just calculates Pi to specific number of digits. What I found was that this was taking longer now, and worse it was taking twice as long as it did on my old laptop. Not good.
I put this down to the drivers and did a system restore. That took an age but eventually succeeded. (Quite impressed with that).
However after doing a system restore SuperPi was still not good and whats more running the windows performance thing resulted in the CPU and memory scores dropping to 4.3. Something is wrong.
I decide to look at CPU and so started up the resource monitor and when running superPi I got something like this
Note the blue line in this picture, this is the measure of maximum frequency. Whats that you may ask, well its the % of the Maximum frequency that the processors are running at. If you have 3GHz processors running at 50% its the equivalent of a 1.5GHz processor (not good). Under normal operating with power management on the % should go up and down to match demand to save processing power. Which is great. However what seemed to be happening was that the % would drop even though the processor was being hammered. Go figure.
At times it went down to 8% when the CPU was being hammered. No wonder things took a long time.
So I then started thinking about power management. I stuck the power management on full power, which has a min processor state of 100%. Still the % of maximum dropped. I rebooted and checked in the BIOS and nothing. I was stumped.
Something was throttling the CPU. I dig some searching and hit upon a post mentioning throttling and CPU temperature and then started putting 2 and 2 together.
This laptop had been running very hot for a while now to the extent that after a reboot sometimes the laptop would blue screen saying the Kernel had been shut down due to CPU temperatures.
I therefore started the process of contacting HP support. I was sure that something had gone wrong with the temperature control, the fan, the processor, something.
After speaking to many people including one person that said
“blow into the fan slot, what does that do ?”
“run a huge app and tell me what it does ?”
Was this person for real. I do wish there was an option on support numbers for “I am an IT person please don’t put me through to a muppet”.
Finally I got them to agree to the processor and board being replaced.
The lady came out on Friday last week and replaced the motherboard and processor. Interestingly she noted that the heat sink compound on the processor on the motherboard was messy and she also had to chip the old compound off the graphics heat sink it was so thick.
The laptop now runs beautifully, its cold, its fast and a joy to use.
I have no idea what component had a problem but if I was a betting man I would put money on the graphics processor heat sink not doing its job and so overheating causing the CPU to overheat and thus all the problems. The GPU is core to most things and interestingly I had seen huge Kernel times when running task manager and one of the things that uses Kernel time is the graphics drivers. If the GPU was cooked then that would have caused a problem.
Below is a screen shot of what happens now. You will see on the right is a graph that indicates CPU usage, where the maximum % goes to ~ 100% when needed and back down again afterwards. Something else I used as CPUIDZ that gives you the % as a frequency, I did find that resource monitor gave me a more accurate figure
The lesson learnt is to understand what your laptop should do and why it isn’t doing what it should. Power management is a good thing on a laptop but its not just power management that controls processor speeds on most servers you will find they shutdown if the processors get too hot.
We are fortunate that some of the SQL Server management team are in the UK and so we’ve arranged with them to run an evening social event.
The guys we have over are Tom Casey, Mark Souza and Eduardo Rosini.
Anyone thats been to SQLBits should know Mark Souza. He is a General Manager in the Microsoft SQL Server development group. He is responsible for all Customer Programs including the Product Group’s Customer Advisory Team. This team specializes in implementing the most challenging customer database applications worldwide.
Tom is Corporate Vice President within the Business Platform Division (BPD). Tom is responsible for the strategy and execution of the division's cloud services initiatives. Casey has overall business, engineering and operational responsibilities for SQL Azure.
Eduardo is corporate vice president of the Business Platform Marketing Group at Microsoft, which drives marketing and strategy for the SQL Server business, middleware, and data modeling tools for the information platform. He’s been at Microsoft a massive 18 years.
The evening is designed around letting you get the answers you want from these guys. So we have 2x30 minute presentations/demos covering SQL Azure, Denali and Crescent. This will be followed by refreshments (including alcohol) and then a Q&A session.
It should be a great evening so make sure you register quick as space is limited.
To register click here
If you work in IT then it is likely that you will be using remote desktop, if your working in operations then you definitely will. Whats more you will be probably connecting to multiple servers at the same time.
There are a few tools for managing this, including one installed by default on windows called remote desktops. This is clunky and really unusable when managing > 10 connections.
To the rescue comes RDCMan from Microsoft http://www.microsoft.com/downloads/en/details.aspx?displaylang=en&FamilyID=4603c621-6de7-4ccb-9f51-d53dc7e48047
This allows you to have a tree view of connections all nicely arranged in a hierarchy and you can connect to them at will and flip between connections easily.
The best thing is credentials. You can store credentials for a folder of connections. That means you can just create a new connection in the folder and hey presto you can now connect to that server using the credentials that you’ve got configured. Really nice. This does mean you have to be extra careful of your main login credentials, once someone was logged in as you they will be able to do the same and so potentially be able to get access to servers and resources that they shouldn’t.
I did have a screen shot that was fully attributed to the owner with a link back to him. However we didn’t see it as positive and so I’ve removed the screenshot and the link. If you want to see what its like just install it.