The Heap™ – Consultancy ©® - 2015-10-05 (page 2 of 2)

« first day (1735 days earlier) ← previous day next day → last day (3136 days later) »

8:03 PM

like this:

SELECT
    SessionID = req.session_id
    , BatchStartTime = req.start_time
    , BatchMilliseconds = req.total_elapsed_time
    , StatementStartTime =
		CASE WHEN stat.last_execution_time IS NOT NULL
			THEN stat.last_execution_time
			ELSE req.start_time
		END
    , StatementMilliseconds =
		DATEDIFF(MILLISECOND,
			CASE WHEN stat.last_execution_time IS NOT NULL
				THEN stat.last_execution_time
				ELSE req.start_time
			END, GETDATE()
		)
    , LoginName = des.login_name
    , SQLCommand= req.command

srutzky

@MaxVernon Thanks! And the commas were at the start of each line originally, but I just hate that ;-). Also, I'm not sure that I am handling the StatementStartTime and StatementMilliseconds calculations with respect to the stats data being for the prior query. I updated the StatementStartTime field to what should be correct, but that same change isn't working for the StatementMilliseconds calculation.

And at this point it is way past lunch and I need to eat so I will step away for a bit and look at it again tonight. Either way, it is pretty darn close :)

@MaxVernon Oh, I know. I would definitely reformat that ;-p

Max Vernon

@srutzky its a pretty nice workaround for a dmv that arguably should just work.

srutzky

Just as you would reformat mine ;-)

@MaxVernon True, but if things just work, I would have a lot less work ;)

Max Vernon

@srutzky ain't that the truth!

dezso

8:24 PM

does the table layout fall apart for you, too?

Q: DB Engine Stolen Server Memory is too high

I get error from System Center Operations Manager (SCOM). How to solve this error? SQL DB 2012 Engine Stolen Server Memory is too high. I run query and get this counters: ╔═══════════════════════════════╦═════════════╗ ║ Stolen Server Memory (MB) ║ 7354.773437 ║ ║ Lock Memory (MB) ...

JNK

looks right to me

swasheck

zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz‌zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz

i wonder what the problem here could be

Q: SQL Server Configuration Manager returns remote procedure call failed

I open the SQL Server Configuration Manager and it returns remote procedure call failed. This is Windows 7 Ultimate version, SQL Server 2008 R2 Express version with advanced services. Previously I installed SQL Server 2008 R2 Express version and since I needed the full text search I uninstall...

sql-server sql-server-2008-r2

JNK

I bet its a bug in SQL Server

Lamak

8:39 PM

that's my guess too

Zane

8:51 PM

"multithreading is for queries that use cpu cycles heavily"

huh?

Max Vernon

> I am generally write down according to bit size

I have big bits and I cannot lie.

Zane

Not sure how to respond to that.

James Lupolt

9:09 PM

@PaulWhite This guy also works for MySQL: dba.stackexchange.com/users/1268/morgan-tocker

I think there is a semi-regular poster who works for Mongo, too.

swasheck

@james ... how's it going?

James Lupolt

@swasheck Not bad, you?

Phil

We have the epic @RolandoMySQLDBA - don't need anyone else! Legend!

James Lupolt

GIVE SOMEONE OTHER THAN ROLANDO A TRY

Phil

Q: Oracle - create changelog query

I came up with an interesting Oracle-db problem and keep thinking about a feasible solution to solve this task. Lets say we got three tables (bold columns are PKs): A (Name, GroupID, versionID, Column1, Column2) B (Name, GroupID, versionID, Column1, Column2) C (versionID, date_created) So it ...

sql oracle

Either I just don't get it, he doesn't get it, or it's just not possible

swasheck

9:16 PM

@JamesLupolt i'm ok. we expand data files when we do index rebuilds but then the space is no longer needed when the rebuild is complete and then we've got a bunch of spare space in the files. i know what you're thinking, "drop your growth factor"

well i dont care

@Phil downboat

James Lupolt

@swasheck There've been a couple cases where I've been tempted to see if sort_in_tempdb avoids that, but I've never bothered so far.

Phil

Index rebuilds? What is that nonsense?

swasheck

@JamesLupolt yeah. that's going to take a lot of exploration for us because ... well ... our queries are such crap that tempdb is generally used for things like spills

@Phil it's an overemphasized maintenance task that makes sql server dbas feel like they're streamlining their io throughput

James Lupolt

SQL Server DBAs (on average) seem to be unique in their focus on index fragmentation

It's definitely a significant problem sometimes of course

But the Oracle DBAs I've talked to are mostly against even thinking about it, and it was rarely something that came up when I was a MySQL DBA

swasheck

@JamesLupolt i fell into stats maintenance because i found it to be a much more significant factor in our performance maintenance here

dezso

9:23 PM

@JamesLupolt are there indexes in MySQL?

James Lupolt

@swasheck yeah definitely, stats problems make queries take orders of magnitudes longer whereas i guess index fragmentation tends to degrade performance linearly

dezso

ducks

swasheck

@dezso yeah, but they're not used

James Lupolt

@dezso yes but only one per query, or something (i don't really know anything about mysql anymore)

swasheck

@JamesLupolt prozactly

James Lupolt

9:24 PM

Do Postgres DBAs care about fragmentation?

dezso

@JamesLupolt seldomly

we do care about bloat

James Lupolt

that's my main concern with fragmentation to be honest

dezso

like I do here

James Lupolt

i've come across some crazy performance problems caused by heavily fragmented tables that are 99% empty filling memory with emptiness

dezso

table bloat in PostgreSQL is mostly handled by the visibility maps

it's still a waste of IO

Erik

9:27 PM

@dezso I find the visibility maps much more useful than the invisibility maps

swasheck

ah hah, hush that fuss

dezso

we have some jobs that rebuild indexes on certain tables, but it is about 2% of the tables in 1% of the databases

RolandoMySQLDBA

@Phil and @JamesLupolt I had Morgan Tocker as an instructor in a Percona Operations Day class years ago. Trust me , he has extra Mojo on me with MySQL.

swasheck

salon.com/2015/10/02/…

billinkc

You're like a small child that wanders in to the middle of a movie and want to know what's happening

swasheck

9:38 PM

howd your sqlsat go?

dezso

A: Stored Procedure slower on one server than the other

It's interesting that SQL Express would run the sproc faster than standard edition, since Express has a 1GB limitation of memory. Please check to see what the max memory settings are set to for Setup 2 (right-click the SQL server > Properties > Memory). I doubt it's set to anything lower than yo...

whoever declined my flag was wrong :D

this is just a relatively informative megacomment

swasheck

@dezso sick burn, bro

dezso

@swasheck I donno wat dat mean

billinkc

@swasheck Not too bad. New venue so a few wee hiccups but barring the drink situation, I heard no complaints

dezso

urban dictionary to the rescue

swasheck

9:47 PM

@billinkc nice

billinkc

Well, I also had earplugs in for the day so that helped

James Lupolt

@RolandoMySQLDBA I went on a Percona class with him a few years ago too. Also a very nice person, in addition to knowing a lot about MySQL.

swasheck

10:32 PM

Sementech AntiVirus just corrupted my ssis package

idiots

James Lupolt

11:32 PM

Sophos shot my dogs

srutzky

11:53 PM

@MaxVernon I believe it is done now. The issue I was having was that the elapsed times in dm_exec_query_stats are expressed in Microseconds. Nice. I have tested it several times and the Statement "start time" and "milliseconds" do appear to calculate correctly. I also found that statement like SELECT 1 and WAITFOR DELAY do not show up in dm_exec_query_stats, so that makes testing a little more difficult ;)

But fortunately SQLCLR allows me to do something that nobody in their right mind would ever even consider imagining doing, but it makes testing of timing issues so much easier.

Max Vernon

@srutzky Yes, the power of SQLCLR is amazing.