The Heap™ – Consultancy ©®

12:03 AM

@MaxVernon Using WAITFOR DELAY doesn't always help if the query I want to analyze completes before the WAITFOR starts, and it always has to. It makes testing Triggers, Transactions, this stuff, etc much harder. BUT, a very simple function that does a Thread.Sleep(MillisecondsToWait.Value); will run once per row of th result set and hence the current query won't finish until I say it can finish!! But that leaves the execution context active long enough to run some tests :-D

Max Vernon

12:20 AM

@srutzky T-SQL is very powerful, but having access to O/S calls just makes it all the more powerful in T-SQL

d'oh - that should read "all the more powerful in SQLCLR"

swasheck

3:03 AM

I AM SQLCLR THE GREAT AND TERRIBLE

billinkc

3:34 AM

Son, you are about to get into a world of hurt. I will unload your appdomain like that

snaps

Paul White

@JamesLupolt Yes I've advertised him in The Heap before. Good to see though.

Paul White

5:36 AM

@dezso Sorry about that, but I still don't agree it should be removed.

I disagree, on balance. The question is already closed, so no new answers can be posted. Anyone finding this Q & A via search will at least find some useful pointers from this answer. It is clearly too long for a comment. Overall, I feel it is better we keep this; deleting it would not improve the site. — Paul White ♦ 2 hours ago

dezso

I've seen that

makes sense

Paul White

2

Regarding link-only answers and a bit about NAA flags. Ripped from Shog

dezso

to show what this one resembles, you should include a water melone, too

Paul White

Nah. That would be not an apple.

flagged as not an apple

dezso

@PaulWhite is the orange an apple?

Paul White

5:46 AM

@dezso No. It should be flagged as not an apple.

dezso

@PaulWhite that's good

I started to lose track :)

Phil

7:35 AM

Mornin'

ypercube

@JamesLupolt urban myth

ypercube

8:42 AM

just 1, the latest message from john to maria. and order this messages to maria by id desc @ypercube — Rick Joe 1 min ago

that's why we shouldn't interact with 1."mysql" and 2.SO users.

How can one order by if the result is only 1 row?

Phil

9:03 AM

At least MySQL doesn't have much scope for returning the resultset in the wrong order, bless it :D

ypercube

9:17 AM

I forgot I had an [sql] hammer at SO: stackoverflow.com/questions/32965978/… ;)

Phil

9:27 AM

Quiet in here today

Colin 't Hart

BOOM!

ypercube

@Colin'tHart that is reserved by @Kermit / vertica questions. Flagged for improper use of "BOOM" ;)

2

Colin 't Hart

Hmm, yes, "vertica crash" does seem to generate a lot of hits on Google :-)

Conversely, the first hit for "oracle crash" is youtube.com/watch?v=-KNql99zT6s

dezso

@Colin'tHart those boats can crash really spectacularly

The Plymouth Capsize Club 2 - Another smashing Sunday!

►

Paul White

9:55 AM

@ypercube Not since he defected. BOOM is available for claiming again, I think.

ypercube

10:20 AM

yeah, I wonder where he is these days.

Phil

Did TomTom ever come back and cause trouble? And the missing moderator too?

ypercube

@Phil the missing mod climbed off his chair.

dezso

@Phil missing moderator?

you mean @jcolebrand? ;)

Phil

No, the denny

ypercube

27

Q: 2015 Moderator Election Results

Database Administrators' fourth moderator election has come to a close, the votes have been tallied, and the new moderator is: He'll be joining the existing crew shortly — please thank him for volunteering, and share your assistance and advice as he learns the ropes! For details on how the v...

> Please also join me in well-wishing mrdenny, who is stepping down after almost 4 years of service to this community.

Paul White

10:40 AM

TomTom

1 1 5 13

sql-server mysql performance database-design sql-server-2012 sql sql-server-2008

Phil

10:51 AM

@ypercube Ah, missed that

Colin 't Hart

11:10 AM

@PaulWhite What was the story here?

Paul White

@Colin'tHart We don't talk about Fight Club.

Colin 't Hart

Must've been bad, judging by the length of penalty.

ypercube

11:25 AM

Yes, I want to delete to whole column, without removing the PK constraint on it, is it possible? — rg666 53 secs ago

speechless

Colin 't Hart

@ypercube Me too. I have no idea how to answer such a ridiculous question.

Paul White

Good grief.

Phil

Terribad

Colin 't Hart

I hope the asker has some good reason for asking this, such as "anonymization" of data by removing the IDs.

Paul White

Goodness knows.

Andriy M

11:38 AM

@ypercube I want to get rid of my clock but keep its alarm system.

3

Paul White

11:55 AM

-3

A: Explaining 2NF vs 3NF with an example

I'm preparing to delete this as it is considered incorrect Subject Name is also a non-prime attribute and it depends on part of the primary key Subject Code (breaks rule - there must not be any partial dependency of any column on primary key). This is prohibited in 2nd Normal Form and should th...

Preparations continue... :)

Mikael Eriksson

Any DBA's in here?

Phil

Nah, we're all pretenders

Mikael Eriksson

I know I am :)

I need a ballpark figure for time to backup and restore a 3TB db.

Colin 't Hart

@MikaelEriksson How long is a piece of string?

Mikael Eriksson

Without using snapshot, just regular backup restore. What would be possible to achieve, Should be possible.

Colin 't Hart

11:59 AM

Which DBMS are you using?

Mikael Eriksson

@Colin'tHart SQL Server (not me). I'm preparing for a discussion with a customer.

Colin 't Hart

Where will you write your backups and perform your restore?

Over network?

Mikael Eriksson

No idea

Colin 't Hart

What type of storage?

ypercube

16 hours

Mikael Eriksson

12:01 PM

Situation is, customer says it takes too long so you have to build this and that in your application. I want to know if the times they say is too long is reasonable or if the guys managing SQL Server on the customer end is no good at what they are doing.

@ypercube That is too long :)

Paul White

I heat 1TB/hr is reasonably easily achievable, with the right hardware and configuration.

Probably faster these days.

Colin 't Hart

@PaulWhite Is that for backup AND restore?

ie dump and reload a 1Tb DB in one hour?

Paul White

@Colin'tHart Not sure. I think it was just one of those. Relying on memory though.

Mikael Eriksson

Half of the data is documents in a varbinary. The request is to store those outside of DB to make maintenance easier. I'm not a big fan of that in this application so looking for other solutions to the problem.

Paul White

e.g. (not exactly applicable, but bear with me)

We Loaded 1TB in 30 Minutes with SSIS, and So Can You - March 2009

Mikael Eriksson

12:04 PM

@PaulWhite That is not too long.

Colin 't Hart

@PaulWhite That's roughly 1/2 Gb per second...

seems fast :-)

Paul White

Certainly compares well with my laptop.

And that was six years ago

So it would be three times slower nowadays and nine times more expensive ;)

Mikael Eriksson

One idea I had was to use partitioning on the identity column keeping older stuff in a readonly filegroup so they could do piecemeal restore.

Paul White

There are certainly multiple ways to make it work.

What's the average/median/most common size of the varbinary documents?

@MikaelEriksson Are they just stored and retrieved, or updated?

Mikael Eriksson

I think this all is about not wanting to spend money on hardware.

@PaulWhite 150k

Paul White

12:08 PM

But ... shiny!

Mikael Eriksson

Currently increasing with 1TB per day

Paul White

Even TomTom couldn't store that on his phone.

3

Mikael Eriksson

About 8000 of them each day.

@PaulWhite Just stored and retrieved. Never updated.

Paul White

Of course - you did say R/O FG.

Mikael Eriksson

Except for the rare occasion when the medical records for a patient should be destroyed for ever and ever. Then there is deletion but that can be handled as deleting references in other tables and do some cleanup job later.

Paul White

12:11 PM

Personally I'd be open to storage outside the database, if there's no good reason to store it there.

Partitioning and piecemeal restore can be tricky and time-consuming to get right. But more fun.

Then there's filetable and filestream, but I have no experience with those.

@Colin'tHart It's all relative though. My single consumer SSD regularly hits 200MB/sec, so 500MB/sec isn't so impressive.

James Lupolt

@ypercube Yes, I'm mostly joking. Even one index per table is not really true.

Mikael Eriksson

@PaulWhite For part of the functionality the blobs need to be available from TSQL and that can be technically challenging. But most of all it is the the uncertainty on my part of leaving it up to a customer to implement a filestorage thing that does the job right and don't spread these document to places where they don't belong. Or even accidentally deleting or something like that. Just feels good to have those updates in a transaction with the rest of the medical records.

A mixup (wrong document for the wrong person) in this could mean life or death for a patient.

There is also a quality assurance thing that we guarantee. The product is CE verified.

Colin 't Hart

@MikaelEriksson Completely agree.

Mikael Eriksson

CE marking

The CE mark, or formerly EC mark, is a mandatory conformity marking for certain products sold within the European Economic Area (EEA) since 1985. The CE marking is also found on products sold outside the EEA that are manufactured in, or designed to be sold in, the EEA. This makes the CE marking recognizable worldwide even to people who are not familiar with the European Economic Area. It is in that sense similar to the FCC Declaration of Conformity used on certain electronic devices sold in the United States. It consists of the CE logo and, if applicable, the four digit identification number of...

Paul White

@MikaelEriksson That would qualify as a good reason.

Mikael Eriksson

12:24 PM

Not to be confused with chinese export.

Thanks all, I will be back for more if I need it.

JNK

mornin all

Lamak

12:54 PM

morning

Tom V

'Noon

ypercube

morning 'merica

Tom V

shouldn't this be 2 questions?

0

Q: Failed scheduled job

I have created 3 jobs in my MS SQL Server The job returns an error on 2. Remove top 4 lines Message Executed as user: myDomain\administrator. The process could not be created for step 2 of job 0xA849BBB21D41B64387336C2CAA7FB632 (reason: The system cannot find the file specified). The st...

sql-server powershell jobs

It's one job, but it's 2 problems

Phil

1:14 PM

zzzzzPhil

Paul White

Mo' jobs mo' problems

Huh that's the user that never accepts answers.

dezso

1:34 PM

@PaulWhite do you text them in these cases to change their behaviour? :)

Paul White

@dezso Yep tried that. No dice.

Can't force them to do it.

JNK

@PaulWhite use the mod tools to get his PW, then log in as him and accept some.

Paul White

@JNK Ah true. Goodness I'm such a n00b.

:-D

Lamak

voted

Paul White

@bluefeet Thanks!

@Lamak Also, thank you.

bluefeet

1:43 PM

@PaulWhite that doesn't involve a lot especially when I'm still half asleep

Paul White

@bluefeet INSERT Coffee INTO Bluefeet;

I forget you lurk.

bluefeet

that I do

Paul White

Community Managers: professional lurkers.

bluefeet

it's required

we have to pass a lurking test

Paul White

Ha ha ha I have a mental image of that now.

Question clarified:

-2

Q: Is deleting primary key attribute possible?

Is deleting the primary key column of a table possible, without removing the PK constraint from it? If it is, how to go about doing so?

database-design

LOL

Lots and lots of love.

Colin 't Hart

1:51 PM

laughing and sighing simultaneously

Is there a word for that?

Paul White

There must be. Sighloffing?

So: instruction to computer: maintain uniqueness and prevent NULLs in this non-existent column please.

computer explodes

7

A: sys.dm_exec_requests - start_time

At the moment this does not seem like a possible value to get, which is unfortunate as this does seem like a handy value to have. Ok, I did find it (mostly). There is a last_execution_time field in the sys.dm_exec_query_stats DMV, though that DMV comes with the following warning: An initial ...

@srutzky This is an admirable effort, but as you note, it would fail for multiple concurrent executions of the same cached plan. Really there's no DMV support for this, we'd need to use Extended Events or Profiler. The requests DMV, for example, is primarily for MARS support. A request is not a statement in a batch, as many people expect.

There's a good reason sp_WhoIsActive does not show elapsed time for the current statement. There's just no safe, computationally-lightweight way to do it.

Phil

2:17 PM

@bluefeet are you enjoying it?

bluefeet

@Phil yes, I love it

Phil

@bluefeet Brilliant :-)

srutzky

2:40 PM

@PaulWhite "as you note, it would fail for multiple concurrent executions of the same cached plan. " ... "requests DMV, for example, is primarily for MARS support.": tru dat, BUT, on a pragmatic level, how often is this information mostly, or entirely, accurate vs inaccurate due to concurrency and/or MARS? How many people use MARS outside of the Lazy Loading feature in Entity Framework?

Max Vernon

@PaulWhite oh, I so badly want to answer that question with a pragmatic explanation of what a constraint is, and why you couldn't possibly want to remove the column underlying the constraint, whilst leaving the constraint in place.

srutzky

@PaulWhite I would suspect that well over 50% of the time this info is "good enough" to get a sense of things. And for the circumstances where it is less accurate, that could be acceptable as long as expectations are set accordingly.

Given that this particular info is mostly needed when something is taking a rather long time to complete, how often would it be the case where that exact same query is also running much faster at the same time in other sessions? Yes, it certainly can happen. But, does it happen often enough to entirely invalidate this info?

swasheck

i've not heard any morph of pragma used in the last 12 months, and now i see it twice in consecutive chat messages of the heap. another reason to love it.

2

srutzky

@PaulWhite "There's just no safe, computationally-lightweight way to do it." Well, not yet ;-). I have an idea or two, though :)

Phil

Shattered again today. Having real problems sleeping

swasheck

2:45 PM

@srutzky a new SQL# assembly?

in other news ...

9news.com/story/news/local/2015/10/05/…

srutzky

@swasheck No, and probably not even SQLCLR. It will be at least a month before I have time to work on it. But I will let you know if it works out :)

Paul White

@srutzky Let's leave the MARS thing for a second - I only mention that because people in general often expect requests to change for each statement in the batch. How often the information would be wrong depends on how often the same plan is concurrently executed. Given that SQL Server is designed to reuse plans and facilitate concurrent execution, the chances seem quite high.

@MaxVernon That's part of the reason I put it on hold ;)

Max Vernon

@PaulWhite I suppose the chances of it helping even one future visitor is pretty low. At least one hopes its pretty low.

Paul White

@srutzky I can't speak to what proportion of the time it would 'work', but I do know I like to be able to trust my monitoring data. Never knowing if it were correct or not would be ... concerning.

swasheck

stated with the suave debonair of a kiwi 007

2

Paul White

2:52 PM

@srutzky It's not just that it might be running faster; it might overlap at all. And you could never know. I'm not saying it's a hopeless idea at all. It is an "admirable effort" given the lack of proper DMV support.

But really, what we need is a DMV that shows per-statement metrics.

srutzky

@PaulWhite I certainly understand the issue of "SQL Server is designed to reuse plans and facilitate concurrent execution", which is why this data is not 100% reliable. But, I am just trying to put things into perspective, balancing what is possible vs what is most likely to actually happen.

I am just saying that as a diagnostic tool, if it was understood that the info is not guaranateed (which seems acceptable given that MS has that warning on the MSDN page for query_stats), then is it better to have something that is more often correct than incorrect, or not available at all because it might sometimes be incorrect?

swasheck

@PaulWhite could query store a step in that direction?

(i've not done much examination of it as it stands)

srutzky

@PaulWhite "what we need is a DMV that shows per-statement metrics." Absolutely no disagreement there :)

Paul White

@srutzky For mine, no. I think we differ in how often the information is likely to be wrong. SQL Servers reuse and concurrently execute plans all the time. I just can't imagine using q_stats would be useful in anything except a single-user test environment. You should see some of the edge cases we turned up while developing the initial release of (open-source) sp_WhoIsActive for example.

@swasheck Not as I understand it currently. It's more about plan changes and performance differences for the whole batch.

swasheck

@PaulWhite oh. bummer.

Paul White

2:58 PM

@srutzky Regarding the accuracy of query_stats, that warning is mostly about being up-to-date and consistent with other DMVs. There's a difference between that and simply wrong information.

I don't want to come across as too negative on the idea though, perhaps it sounds that way. Apologies if so.

@swasheck One never knows what might be in the pipeline though.

The whole instrumentation/DMV/XE split is odd though. It forces tools like Plan Explorer to run XE sessions in the background, retrieve some information from DMVs, and some via SET statements, for example.

Really, the idea of starting and stopping a Profiler Trace or XE session at the same time as the DMV-accessing query is not much different.

That seems like a cleaner and more reliable solution to the question that using query_stats.

swasheck

@PaulWhite supporting a legacy decision while replacing it with something that they wish they hadn't done. THUMBS UP!

srutzky

@PaulWhite All of that is understood. And no, you are not coming across as negative, at least not to me. Absolutely nothing wrong with sharing knowledge / perspective / experience / opinion.

Paul White

@swasheck Double thumbs up.

It'll all be fixed in U-SQL of course.

Max Vernon

@PaulWhite YouSQL ?

swasheck

@PaulWhite the comment on that post about waiting for v-sql was really pretty funny

Paul White

3:08 PM

Introducing U-SQL – A Language that makes Big Data Processing Easy - The Visual Studio Blog - Site Home - MSDN Blogs http://blogs.msdn.com/b/visualstudio/archive/2015/09/28/introducing-u-sql.aspx

Tweeted by SQL_Kiwi on October 6, 2015 at 2:12 PM

T-SQL + C# = U-SQL?

swasheck

> Can't wait to try it.

I think an article with a few concrete examples comparing T-SQL vs U-SQL (including statistics, execution plans, ...) would be really interesting.

When is V-SQL planned ? :)

Paul White

Ha.

> So we won't see this as a replacement for T-SQL in the MSSQL Server? That's where I'd like to see it most.

> [Michael Rys] Not at this point. But if you ask the SQL Server team nicely, maybe they will listen :).

swasheck

> Exciting.

Does it optimise the queries of a C# developer with limited DBA skills? :P

JNK

@PaulWhite U-SQL is too close to the second-person MySQL

"U-SQL - MySQL for others™"

Paul White

Personally, I'd love to see U-SQL for box SQL Server 2016.

They could fix the name :)

Goodness knows T-SQL could use some love. Probably LOL.

swasheck

3:14 PM

@PaulWhite t-sql ... love you long time

Paul White

@swasheck "Morph of pragma" ???

That's way above my pay grade.

What's it mean?

swasheck

@PaulWhite you're just using the wrong scale

@PaulWhite any forms of the root word "pragma"

Paul White

@swasheck What was the example?

swasheck

@srutzky and @max both used "pragmatic"

Paul White

Ah.

swasheck

3:17 PM

in consecutive chat messages

Paul White

is learning from @swasheck

swasheck

a first in the history of the universe

Paul White

> [Michael Rys] I would be very happy to help other teams to provide an on-premise offering.

He's keen :)

Max Vernon

Microsoft Surface Book just announced; a laptop that is Surface.

swasheck

randy, even

srutzky

3:22 PM

@swasheck All I can say is, using the term "pragmatic" was the only practical thing to do ;-)

swasheck

@MaxVernon so ... uhhh ... what? huh? not even sure what that is

Max Vernon

@swasheck 13.5" screen, with 6 million pixels (267 dpi), with a built in keyboard.

Paul White

Nothing much happening today then.

swasheck

@MaxVernon so it's like a step back to the twisty-foldy half tablet things that HP was pushing a few years back

Max Vernon

@swasheck nope. its a legitimate laptop, just with the Surface brand 'cuz its made by Microsoft

swasheck

3:25 PM

@MaxVernon so was hp's ... a laptop with a twisty foldy touch screen. anyway. keeping up with folks' "innovation" is exhausting. it'd sure be nice if we could reduce clutter and use all of this technology to do some good for humanity.

sorry. must be the amarula talking

Max Vernon

@swasheck mmmmm Amarula!

Paul White

live.theverge.com/…

Max Vernon

it has a 12 hour battery life, with an Nvidia GPU.

swasheck

amarula+cola+milk ==== pure heaven

Lamak

@PaulWhite gizmodo.com/…

swasheck

3:27 PM

STOP THE PRESSES!!!! WE'VE INVENTED A ........................................

LAPTOP

Paul White

Shut up and Take My Money.

srutzky

@PaulWhite Interesting. So first they bring SQL / set-based semantics into .NET, which gave us LINQ (and then Entity Framework), and now they are bringing LINQ (set-based .NET) into SQL Server (Azure SQL Database, whatever). This is very incestuous ;-). But lessons-learned do make for better products :).

Max Vernon

it has a PCIe SSD !!!

Paul White

@srutzky It's all very straightforward isn't it ;)

@MaxVernon That's good, right?

Phil

perl,bash,ruby,python,sed,awk,COBOL ..... the list goes on... — Phil 7 secs ago

One of those may have been a troll

Max Vernon

3:30 PM

@PaulWhite lol. So much faster than having the SSD connected any other way. You'd likely see 500,000 iops on that machine.

on a notebook

Paul White

@MaxVernon Genuine question. I had no idea. Too busy applying for a loan.

srutzky

@PaulWhite This is all very "Chinatown". Is U-SQL a descendant of T-SQL, or a sibling? Or maybe chicken / egg analogy is more appropriate?

swasheck

@Phil ruby

Paul White

Exactly. Is it mostly SQL or mostly C#?

Who won? The DBAs or the Developers? :)

Max Vernon

@PaulWhite it attaches the SSD directly to the system bus, bypassing the need for SATA, or SAS connectivity.

srutzky

3:32 PM

@PaulWhite I almost just replied with: "No, SQL#". ;-). I guess it would be SQL.NET?

Paul White

Ooo. Take. My. Money.

Max Vernon

@PaulWhite no doubt!

Paul White

The screen comes off.

Max Vernon

and the keyboard does come off!

lol!

Lamak

@PaulWhite wow, that's.....cheap

srutzky

3:36 PM

@PaulWhite "Who won? The DBAs or the Developers?" In reality? The not-so-many of us who understand both SQL and C#. It just thins the herd a bit, creating a higher barrier to entry in the field. But I suppose that is progress. I have an uncle who works on cars and he was telling me how things have changed over the years, where it used to be that anyone at least had a chance of fixing a car, but now it is mostly computerized / electronic and requires quite a bit more education.

Max Vernon

and guess what, they'll have 'em available October 26th. In Seattle.

Paul White

Bah! What a year to skip Summits.

Max Vernon

@PaulWhite yup.

Paul White

@srutzky True. And some expensive equipment.

Max Vernon

wow that rendering performance is unbelievable.

srutzky

3:39 PM

@PaulWhite So DBA's have more ability to do wonky things, and developers have even more ability to write procedural / non-set-based SQL than they do now ;-)

And both sides now have less reason to talk to each other ;)

Paul White

Aug 11 at 21:50, by Paul White

With great power comes a great lack of responsibility.

srutzky

This is one of the main down-sides to SQLCLR: it allows for someone who is really good at writing good code in one paradigm to use that skill to write horribly bad code in another paradigm that has different rules (rules that they -- often enough -- don't have time and/or desire to learn).

Paul White

To be honest, I just want the basics. Strong types and a rich library of functions. Closer integration so data access is faster.

Is it my imagination or is Satya channelling Steve Jobs here?

Phil

`ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/AN
Y/SOME subquery'` -- le sigh

billinkc

3:57 PM

What's a satya

Phil

Satya Nadella

Satya Narayana Nadella (/ˈnədɛlæ/; born August 19, 1967) is an Indian-American business executive. He is the current chief executive officer (CEO) of Microsoft. He was appointed as CEO on 4 February 2014, succeeding Steve Ballmer. Before becoming CEO of Microsoft, he was Executive Vice President of Microsoft's Cloud and Enterprise group, responsible for building and running the company's Computing Platforms, Developer Tools and Cloud Computing Services. == Early lifeEdit == Nadella was born into a Telugu speaking family in Hyderabad, Andhra Pradesh (now Telangana), India. His father, Bukkapuram...

billinkc

You should just assume everything that comes from my fingers is troll

Paul White

@billinkc Enjoy your last year as an MVP :)

billinkc

Since you won't be at the Summit, I think enjoyment is a forgone conclusion

Fair enough

I disagree, on balance. The question is already closed, so no new answers can be posted. Anyone finding this Q & A via search will at least find some useful pointers from this answer. It is clearly too long for a comment. Overall, I feel it is better we keep this; deleting it would not improve the site. — Paul White ♦ 13 hours ago

Paul White

4:12 PM

@billinkc :'(

@billinkc That was as much aim at the NAA flagger as your comment.

billinkc

I did both

ConcernedOfTunbridgeWells

@PaulWhite Very, very shiny - especially the new NVMe ones.

Paul White

@ConcernedOfTunbridgeWells NVMe?

ConcernedOfTunbridgeWells

@PaulWhite A drive connection standard that more or less sits directly on the PCIe bus without an intervening SATA layer. It can process some ungodly number of parallel requests and is very shiny and fast indeed.

There is also a version that uses the same physical connector as SATA

Paul White

Ooo I do like shiny!

Phil

4:26 PM

The new Intel motherboards & chipsets do look very shiny indeed

Max Vernon

uniqueidentifier hell much?

0

Q: Howto Identify Clustered Index Candidates

I find myself in a situation where I have inherited the db and people complain about database performance. I identified about a dozend of tables not having a clustered index or a not carefully planned clustered index, some of them with lots of data and frequently used. I am actually struggeling t...

sql-server index-tuning clustered-index uniqueidentifier

ConcernedOfTunbridgeWells

@PaulWhite I'm waiting for this shiny thing.

news.lenovo.com/news+releases/lenovo-thinkpad-p50-p70.htm

Laptop with xeon CPU and 64GB of RAM, plus (IIRC) NVMe compatible drive interfaces.

Shinier than a really shiny thing.

4

JNK

@MaxVernon yiiiiiiiiiiiiiiiikes

Max Vernon

@JNK yah, not touching that with a barge pole.

Paul White

@ConcernedOfTunbridgeWells Looks lovely. Will it be cheap?

:)

JNK

4:33 PM

from the link:
> Pricing for the P50 begins at $1599, and the P70 begins at $1999.

Paul White

Oh I missed that.

ConcernedOfTunbridgeWells

By the time I've added 64GB of RAM and a few TB of SSD storage - probably not.
But, still quite a lot cheaper than the software installed on it.

Paul White

Right.

Kris Gruttemeyer

Hey guys, my google-fu is failing me. Is there a standalone download for BIDS for SQL2005 x86 that anyone is aware of?

Heck, I'll even take the x86 ISO files. We only have x64 here. All I need is BIDS

Avarkx

microsoft.com/en-us/download/details.aspx?id=17708

Pretty sure that'll get you the x86 binaries

Paul White

4:44 PM

Note that this version has big limitations as it "provides the ability to create and edit Reporting Services projects that are reporting on LOCAL relational data in local SQL Server EXPRESS databases. — SomeGuy Jun 8 '12 at 14:14

Mike Fal

@KrisGruttemeyer Stupid question.....why wouldn't you just use the current tools?

Paul White

@Avarkx ^^^

Avarkx

Ahh, so you'd need the SE+ x86 installer regardless, I thought you'd get away with it

Paul White

@KrisGruttemeyer What's the context? Are you licensed, but lost the media?

Kris Gruttemeyer

@PaulWhite We are licensed. We have 2005 SSRS x64. One of our user has an x86 machine and needs 2005 BIDS x86. I believe its because the initial RDL files were made in 2005, using any different visual studio would cause them to be upgraded and not backwards capable, right?

@MikeFal See ^^^

Paul White

4:47 PM

@billinkc Do you know?

Honestly I don't.

Mike Fal

It would upgrade them, yes. But I guess the question becomes why not upgrade them?

Kris Gruttemeyer

I know its a strange question, for sure. We are upgrading the SSRS servers to 2014 in a few months (we finally brought our main prod box to 2014) so this is just a band aid for the time being. I may just need to install it on a sandbox that is x64

@MikeFal To not muddy the waters with the other devs who need those reports in bids 2005

Like i said, its jsut a bandaid for a few months until we upgrade to SSRS 2014

Mike Fal

Oh, the RDLs should still work on 2005 even with the more current version of the tools, I believe. I think only the project files get upgraded so you couldn't open them in BIDs. Everyone else would have to upgrade.

Kris Gruttemeyer

Correct, sir

Mike Fal

But I would also posit that your devs are apparently using software that's 10 years old.

Kris Gruttemeyer

4:51 PM

Also very correct. Plans to upgrade are approved and in motion.

billinkc

ahoy

No download available for BIDS 2005. It's only available on the media

Kris Gruttemeyer

Alright, thanks for the info @billinkc. Gonna go digging through the filing cabinet of ancient software

billinkc

BIDS (Visual Studio) only comes in 32 bit flavor though so even though it's on the 64bit disk, it will install as 32

Kris Gruttemeyer

Yeah, the problem is to get the installer to fire up, it needs to be x64

If I'm not back in 30 minutes, assume the worst has happened and I've succumbed to 3.5" floppy

billinkc

Depending on the users permissions, there is a Click-Once app that you can fire off from the report server and that will allow them to author reports

swasheck

4:59 PM

@MikeFal any neat powershell tricks to find to which servers i am currently connected via RDP?

billinkc

Not as nice as thick client (for v1.0 - v3 is just fine)

swasheck

google is providing unsatisfactory resolts

billinkc

@KrisGruttemeyer No MSDN license for you?

Kris Gruttemeyer

@billinkc Unfortunately, no. It's in the budget for next year though

billinkc

How quaint

Mike Fal

5:01 PM

@swasheck What, like disconnected sessions? You want to sweep servers to find ones that have RDP sessions you own?

swasheck

@MikeFal yes but if you dont have anythign dont fret

Mike Fal

@swasheck Let me think about that.

billinkc

@KrisGruttemeyer Oh, durrr SQL Server Express Edition with Advanced Services microsoft.com/en-us/download/details.aspx?id=5897

That should have SSRS authoring in it

Zane

Ooooo SQL Saturday Chicago.

I'm into that.

Kris Gruttemeyer

Youre not going to believe it, I found sql 2005 standard edition x86 DVD media

Zane

5:04 PM

gross

Kris Gruttemeyer

I feel dirty

Mike Fal

@swasheck Server version?

swasheck

@MikeFal yes

Mike Fal

Ahem. What version of server? 2008 R2? 2012?

swasheck

yes ... and ...

Mike Fal

5:06 PM

So a mix?

swasheck

anyway ... i may have found the issue

stupid phone

Mike Fal

It looks like it's pretty easy on 2012 and 2012 R2. 2008 R2 is not as easy.

swasheck

what about 2003?

:D

billinkc

Just reboot the machine

With an axe

Mike Fal

And fire

Kris Gruttemeyer

5:08 PM

There it is

imgur.com/3TagXhS

billinkc

While we don't do licensing questions... if you install standard edition on server A, report developer on machine B wants to have BIDS so they can develop reports. I assume installing BIDS (which only exists on server disk) on their machine would be a license violation

Kris Gruttemeyer

5:23 PM

I wouldn't think so, otherwise people would have to license BIDS for each and every machine that its developers use. I know MS is a money-hungry, greedy machine but thats alot even for them

Wouldnt that mean that SSDT would also ened to be licensed, per machine?

It's Free-ninety-nine

scottandscottllp.com/main/SQL_Server_Licensing_Perils.aspx

Zane

The key is simply to not let anyone know you're commiting a liscense violation. Especially if they work at Microsoft.

JNK

I think BIDS is free

it's basically a skin on VS

same as SSMS

Zane

@JNK That's what I thought as well.

JNK

it's also mostly useless without a licensed product of some sort

Zane

social.msdn.microsoft.com/Forums/en-US/…

JNK

5:31 PM

wunderbar

Kris Gruttemeyer

Thanks so much for all the help guys!

Zane

7:01 PM

/crickets

Max Vernon

7:29 PM

bluefeet

ew

Max Vernon

7:47 PM

mmmm tasty. Looks like a nice crunch.

:-)

jcolebrand

8:02 PM

@dezso :p

dezso

@jcolebrand good to see you here, sometimes

ypercube

8:14 PM

Is this a good enough example of the queries returning different results?: SQLfiddle. Unless there is some FK relationship between the 2 tables, the queries are not equivalent. — ypercube 12 mins ago

I forgot to add "if Hibernate is producing this 2nd query, fire him"

@MaxVernon ayyyeeeeee

Max Vernon

8:42 PM

nice, succinct answer, @AaronBertrand

Max Vernon

9:27 PM

Nope, just a gentle push in the right direction; we love having new users on board, and want everyone to win. — Max Vernon 18 secs ago

sometimes I even surprise myself.

Transcript for

The Heap™ – Consultancy ©®