SQLBits 7 Training Day – SQLOS with Maciej Pilecki
As a SQL Server Professional I love SQLBits, I was there at the first one and I’ve attended most of them since but until now I’d not managed to attend one of the paid training days – mainly because of the cost. It’s not that the cost is high, £250 for a day’s training from some of the best minds in the industry is quite frankly a bargain but it’s still a lot if you’re paying out of your own pocket and my previous employer had a bit of a weird training policy. Thankfully now I’m in the situation that whilst having to be prudent with budget if I really think something is good value for money and worth spending the time on my employer is likely to support me so I finally managed to attend a Thursday pre-conf event.
Choosing one of the courses was quite an ordeal and I was very tempted by Brent Ozar‘s Virtualisation and SAN course, part because having seen him before I know he’s a great speaker and also because it’s going to be directly relevant to my work very soon. In the end I chose Maciej Pilecki‘s course on the SQLOS because the more I understand about SQL Server’s internals the more I understand everything else I learn about SQL Server – I read Kalen Delaney’s Inside SQL Server 2000 cover-to-cover several years ago and it pushed my understanding of the field forward immensely.
I won’t regurgitate the content of Maciej’s course but he effectively covered a massive range of topics and introduced me to several new concepts as well as strengthening my knowledge of others, the rough outline was:
- SQL OS history, services and DMVs.
- Using Affinity Masks effectively.
- Non Uniform Memory Access (NUMA)
- Software NUMA.
- Configuring TCP-NUMA affinity.
- Virtual Address Space on 32 & 64-bit
- AWE and Large Pages
- Buffer Pool architecture and memory allocation.
- eXtended Events
Over then next few weeks I may blog some more detail of what was covered in the course but I’ll only do so if I find the time to put into some of my own research and write my own examples – it’s OK to stand on the shoulders of giants but it’s not OK to copy work from over their shoulder!
Maciej was a great speaker, extremely enthusiastic and well-deserves his reputation as the Dr. House of SQL – out of the many hours of training I’ve received in my career this was definitely one of the most beneficial and ‘action packed’ – all killer, no filler. If you ever get the chance to check-out one of his courses in future I’d recommend it, his next public session will be in Germany (click here for more info).
Categories: DBA, Events, Microsoft SQL Server Tags: CPU, DMV, Maciej Pilecki, Memory, Microsoft SQL Server, NUMA, SQL Server, SQLBits, SQLOS
Creating Databases and Users in MySQL
Historically I’ve been a Microsoft SQL Server guy but I’ve been doing quite a bit of query & analysis work on MySQL lately, though I’ve never performed any serious MySQL DBA. Well, the other day I was asked by a friend how to create a user and allow read/write access to a newly created database and this had to be done in SQL (i.e. no GUI tools or PHPMyAdmin).
Since I was starting from scratch I thought I’d put together a little script to create the database, add some data, create a user, prove that the user had write access and then tidy up after myself (always good to do!).
CREATE DATABASE ash_db;
USE ash_db;
CREATE TABLE ash_tbl (id INT NOT NULL,name CHAR(50) NOT NULL);
INSERT INTO ash_tbl (id,name) VALUES(1,'Ash');
SELECT * FROM ash_tbl;
CREATE USER 'ash_user' IDENTIFIED BY 'ash_pass';
GRANT ALL PRIVILEGES ON ash_db.* TO ash_user;
-- LOGIN AS ash_user THEN EXECUTE THIS...
INSERT INTO ash_tbl (id,name) VALUES(2,'Burton');
-- THEN LOG OUT AND COME BACK HERE
SELECT * FROM ash_tbl;
DROP TABLE ash_tbl;
DROP USER ash_user;
USE information_schema;
DROP DATABASE ash_db;
CREATE USER 'ash_user' IDENTIFIED BY 'ash_pass';
GRANT ALL PRIVILEGES ON ash_db.* TO ash_user;
-- LOGIN AS ash_user THEN EXECUTE THIS...
INSERT INTO ash_tbl (id,name) VALUES(2,'Burton');
-- THEN LOG OUT AND COME BACK HERE
SELECT * FROM ash_tbl;
DROP TABLE ash_tbl;
DROP USER ash_user;
USE information_schema;
DROP DATABASE ash_db;
