Reputation
240
Top tag
Next privilege 250 Rep.
View close votes
Badges
2 8
Newest
 Caucus
Impact
~19k people reached

  • 0 posts edited
  • 0 helpful flags
  • 14 votes cast
Feb
10
comment Speeding up insert in SQL Server
Twice now, I have found that spool was a result of an implicit cast. Look carefully at all your operators and see if you can find any implicit casts
Jan
21
comment ORDER BY and comparison of mixed strings of letters and numbers
I suggest you create a calculated column which pads each numeric component to a large length (i.e. 10 zeros). Since the format is pretty arbitrary you'll need a pretty big inline expression but it's doable. THen you can index/order by/where on that column as much as you like.
Jan
18
comment Executing SSIS Package from a sproc with different user privileges
Thanks for coming back and posting the solution. Now when you have this problem again and forget what you did, you can search on the web and you'll find your own solution!
Jan
10
comment Which database should I look at for analyzing ~30gb, 700M records dataset?
If your queries consist of aggregate functions and group by's then I suggest you use some kind of cube tool instead
Jan
10
comment SQL Server view query generating too much temp
sp_addextendedproperty is irrelevant. All it tells you is that the builder of the view has dubious SQL skills because they built it in a designer window rather than writing a query. I suggest you remove all of the sp_addextendedproperty part and include all of the view definition. Then indicate how many records each table has, then press CTRL-L to get the query plan and post that.
Dec
9
comment Are these database indexes correct for my search
The primary purpose of an index is to improve read (and sometimes write) performance. Do you have a read performance issue? If so, one option is to create index(es). The order of the columns in the index is important. I suggest the first column should be dUTCDateTime, not iAssetId.
Dec
9
comment Are these database indexes correct for my search
Since you are ordering on dtUTCDateTime, and you are probably doing range searches on it, it would make sense to create a clustered index on that column and a non clustered index on iAssetId. But there are many possible answers. You need to 1. Measure insert and select performance and capture the query plan, 2. Apply Indexes 3. Measure insert and select performance and capture the query plan. 4. Repeat from 2, but with a different set of indexes. Then you can make a decision on what is the best index. Heaps aren't always bad as long as you have a reason for implementing it that way.
Nov
30
comment What's the best solution for distribution of one database over two servers?
Why not just put all the ram and CPU on one server? What do you think you'll gain from two servers? Do you have a perforamance issue? Have you worked out what the bottleneck is?
Aug
3
awarded  Caucus
Aug
3
awarded  Constituent
May
29
comment SSIS Package Error when Scheduled in Job
That's a great article because it gets into the guts of things. But a much simpler solution is to give the SQL Agent service an appropriate account, rather than give the incorrect SQL Agent account sufficient DCOM access
May
11
revised SSIS Packages get stuck in SQL Agent jobs
added 322 characters in body
May
11
comment SSIS Packages get stuck in SQL Agent jobs
If you're interested I narrowed this down to our SSIS framework which is called 'BIPA'. I deleted the BIPA logging add in and we no longer have this problem. Based on the .net wait's happening in SSIS I believe it just couldn't instantiate the provider somehow. Why it only does this at night I don't know.
May
8
comment Script to see running jobs in SQL Server with Job Start Time
For me this returned the correct recordset - currently running jobs
May
8
comment Script to see running jobs in SQL Server with Job Start Time
For me this query returned disabled jobs that aren't actually running
May
4
comment Can't start MSSQLSERVER service
didn't work? What was the error? Does this help: eukhost.com/forums/f15/…
Apr
24
comment SSIS Packages get stuck in SQL Agent jobs
Thankyou Aishwary but the packages run fine 95% of the time, and when they stop, they all stop (including for example backup plan packages which I didn't develop). I don't have any transactions in any package. If this was the case I would see some indication of database activity but there is none.
Apr
24
comment Advantage of exposing data model to customers using our product
Are you liable in any way of a customer imports rubbish and/or error causing rows? Does the import table have sufficient constraints, foreign keys, unique constraints, check constraints to stop people importing rubbish? For example if you have a report which joins on a field, and the field is incorrectly duplicated, it will duplicated all joined tables as well, causing incorrect data.
Apr
24
answered Receiving the following error in step failure of an agent job:
Apr
23
comment SSIS Packages get stuck in SQL Agent jobs
OK it happened again. I fire up Process Explorer and took a look at the DTExec process (which is doing nothing). It had 10 threads all in various wait states. There were 6 mscorwks.CreateApplicationContext threads in state Wait_UserRequest. This is not my area of expertise and I don't think it's telling me anything new.