SQL Server diagnostics notes

This week’s amusement: performance of a recently-implemented Stored Procedure dropped and dropped to intolerable levels. This system cannot be easily upgraded. Significant fragmentation is involved. Memory pressure is involved. Inserts to table variables in multiple stored procs are involved. This post records some investigative googling.

Adjusting file autogrowth in SQL Server production systems:

https://serverfault.com/questions/31499/can-i-change-a-live-sql-database-from-autogrow-safely

Scope of Temporary Objects:

https://www.google.com/search?q=sql+server+scope+of+temp+tables&oq=sql+server+scope+of+&aqs=chrome.0.0j69i57j0l4.8990j0j4&sourceid=chrome&ie=UTF-8

Scope of table variable and temp table https://social.msdn.microsoft.com/Forums/sqlserver/en-US/02337dd5-5cfd-40d8-b529-12dc557d6a7e/scope-of-table-variable-and-temp-table?forum=sqltools

Temp tables and table variables scope and persistence

https://www.sqlservercentral.com/Forums/Topic1180410-392-1.aspx

http://www.toadworld.com/platforms/sql-server/w/wiki/10282.temporary-objects

http://www.toadworld.com/platforms/sql-server/b/weblog/archive/2013/02/19/check-if-a-temporary-table-exists

IF object_id(‘tempdb..#MyTempTable’) IS NOT NULL

BEGIN

<TSQL Batch>

END

http://www.toadworld.com/search#q=temporary%20objects&group=6

 

Investigating performance of stored procedures:

https://www.google.com/search?q=sql+server+which+stored+procedures+are+running&oq=sql+server+which+stored+procedures+are+running&aqs=chrome..69i57j0l5.7751j0j7&sourceid=chrome&ie=UTF-8

https://www.red-gate.com/simple-talk/sql/performance/which-of-your-stored-procedures-are-using-the-most-resources/

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-procedure-stats-transact-sql

https://stackoverflow.com/questions/25846306/check-if-stored-procedure-is-running

After a bit of research on MSDN (https://msdn.microsoft.com/en-us/library/ms189497.aspx) I was able to figure out the following query that will provide the list of running processes:

select
object_name(st.objectid) as ProcName
from
sys.sysprocesses as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
where
object_name(st.objectid) is not null

Currently running procedure name: https://www.sqlservercentral.com/Forums/Topic813548-146-1.aspx

Fragmented database files / VLF counts:

https://www.google.com/search?q=reduce+number+of+vlfs&oq=reduce+number+of+vlfs&aqs=chrome..69i57.5927j0j7&sourceid=chrome&ie=UTF-8

High Virtual Log File (VLF) Count

http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/ including :

How Do I Lower a Database’s VLF Count?

Once armed with a list of databases that have high VLF counts, the next step is to shrink the logs to as small as possible then grow them back to the original size, ideally in a single growth. This is best done during off-peak times

Transaction log performance:

https://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/

https://blog.sqlauthority.com/2011/01/02/sql-server-reduce-the-virtual-log-files-vlfs-from-ldf-file/

Average Disk Queue Length:

https://www.google.com/search?q=sql+server+disk+queue+length+high&oq=sql+server+disk+queue+&aqs=chrome.2.0j69i57j0l4.5140j0j7&sourceid=chrome&ie=UTF-8

https://www.mssqltips.com/sqlservertip/2329/how-to-identify-io-bottlenecks-in-ms-sql-server/

https://www.sqlservercentral.com/Forums/Topic1296901-146-1.aspx

SQL Server disk performance metrics – Part 2 – other important disk performance measures

How To: Troubleshooting SQL Server I/O bottlenecks

https://dba.stackexchange.com/questions/17629/cause-of-high-disk-queue-length-in-sql-server

Increase speed of inserts:

https://www.google.com/search?q=sql+server+do+indexes+speed+up+inserts&oq=sql+server+index+spedd+up+&aqs=chrome.2.69i57j0l5.9895j0j7&sourceid=chrome&ie=UTF-8

https://stackoverflow.com/questions/7090243/sql-insert-speed-up

Indexes on Table Variables: https://stackoverflow.com/questions/886050/creating-an-index-on-a-table-variable

Indexes on Temporary Tables: http://weblogs.sqlteam.com/mladenp/archive/2008/12/29/SQL-Server-Management-Studio-2008-suggests-missing-indexes-with-actual.aspx

 

Posted in Linkdump, Programming, SQL Server, System/DB Administration | Leave a comment

Enum tip: Initialized

When using a custom Enum to control features like application states, I always include an explicit Initialized state. This eliminates any uncertainty of which value to use for a necessarily-initialized variable that may not have a defined state- for example, an out parameter.

Posted in Uncategorized | Leave a comment

Tips for adjusting TableLayoutPanel

UI adjustments feel clumsy until you struggle through several, but there are some techniques that can help. It’s obviously better to see these in use, but a list is at least something to start with.

  • TableLayoutPanel displays a flyout menu that can be used to adjust dimensions by hand. This opens a dialog with a table of Columns, or Rows if you select that from the droplist. Each row/col has an Absolute or Percentage value. ALWAYS give one of the entries a percentage value- that’s the one that will expand when the user stretches the screen. The exception is when nothing should ever change size. You can make every row exactly the same size, match precise margin/border measurements, etc.
  • After you establish which rows/columns are absolute and which are percentage, you are able to manually adjust the absolute rows/cols by dragging on the UI. This is almost always faster than entering numbers, and usually alleviates the need to know those precise margin/border measurements…
  • Form fonts will auto-scale in the tables. This works well for normal values, but extreme values can look stupid. So be it. Users might deny it, but they know better than to use font size 48 on data entry forms. Users who try to break things get no pity from me.
  • Anchor and Dock properties are similar in practice. I normally use Dock (Fill), but I think Anchor (L R T B) would work equally well. Docked objects cannot be auto-moved; Anchored can. There are pros and cons to each.
  • In really complicated UIs that have several nested TableLayoutPanels or Splitters, always give those nested objects a name so you know what you have selected as you rearrange the UI. This can be far more important than it sounds, especially for redesign.
Posted in C#, Musings, Programming | Leave a comment

tip: passive error and warning messages

It’s best to put error/warning messages in very passive, non-blaming language, especially if users might see them. Avoid exclamation marks unless something is seriously wrong and they need to take immediate action, and in that case tell them which actions to take.

It can be difficult to give non-technical people meaningful info, with enough tech detail for the dev to actually find and fix a problem. Specifically, the example below is validating data prior to record creation. The message could be phrased like (“Sorry, there was a problem saving the programming step.\n\nError details: missing ID. Mes_product_id:” + mes_product_id +” Mes_route_step_id:” + mes_route_step_id)

//Check that all Guid strings are not empty
if (mes_product_id.Equals(“”) || mes_route_step_id.Equals(“”) || mes_station_id.Equals(“”))
throw new Exception(“At least one ID is empty!”);

And then to be super-nice, you might even help them out with some instructions like “try again by doing this thing instead”, or “contact your supervisor or MES Admin if this problem continues.”

Posted in Business, Musings, Programming, System/DB Administration | Leave a comment

no comment.

So apparently the comment system is borked. I’ll need to fix it. Sorry about the inconvenience!

This is what I get when posting a comment:

500 Internal Server Error Internal Server Error The server encountered an internal error or misconfiguration and was unable to complete your request. Please contact the server administrator, and inform them of the time the error occurred, and anything you might have done that may have caused the error. More information about this error may be available in the server error log.

 

 

Posted in Uncategorized | Leave a comment

Visual Studio Code

https://code.visualstudio.com/docs/?dv=win

https://github.com/electron/electron

Posted in Uncategorized | Leave a comment

Windows 10 wireless displays

https://www.hanselman.com/blog/UseASecondLaptopAsAnExtendedMonitorWithWindows10WirelessDisplays.aspx

Cool!

Posted in Linkdump, Musings, System/DB Administration | Leave a comment

String.Format codes for DateTime etc

HH:mm:ss.fff

HH = 24-hour clock. hh=12-hour = tt=am/pm

MM/dd/yyyy

yyyy = 4-digit year.

https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings

Posted in Uncategorized | Leave a comment

Clone programming steps

— list products that might need to be programmed.

SELECT name,revision, product_id FROM qs_products where name like ‘277000-200-%’

— list current programming steps

select p.name,p.revision,steps.* from [dbo].[ACA_swprg_programming_steps] steps
INNER JOIN qs_products p ON p.product_id = steps.mes_product_id

— after updating the TO and FROM UIDs, clone the programming step.

INSERT INTO [dbo].[ACA_swprg_programming_steps]
SELECT
[programming_step_id] = NewId()
,[mes_product_id]=’7CCD041D-352E-48E3-A581-0A99B148568B’
,[mes_route_step_id]
,[mes_station_id]
FROM [dbo].[ACA_swprg_programming_steps]
WHERE [mes_product_id] = ‘FC643AFA-6FCA-405A-867C-A4F22A17C8F0′

— after updating the TO and FROM UIDs, clone the programmed parts.

INSERT INTO [dbo].[ACA_swprg_software_parts]
SELECT
software_part_id=NewID(), programming_step_id=’D076393C-A968-4D50-9A4F-ED80007BACD4′, part_number, CRC, slot, tracked, storage_location, bin_filename, cfg_filename, mes_component_id=NULL
FROM
[dbo].[ACA_swprg_software_parts]
WHERE [programming_step_id]=’9CB96871-0F82-4194-81FB-C22DF950CA57’

–finally, populate the component_ids. No TO/FROM to change; this populates only the component_ids which should not be NULL.

UPDATE [dbo].[ACA_swprg_software_parts]
SET [ACA_swprg_software_parts].mes_component_id=cpn.component_id
FROM [dbo].[ACA_swprg_software_parts] sp
INNER JOIN [dbo].[ACA_swprg_programming_steps] prgstep ON prgstep.[programming_step_id] = sp.[programming_step_id]
INNER JOIN qs_components cpn ON cpn.product_id=prgstep.[mes_product_id] AND cpn.part_number = sp.[part_number]
WHERE sp.mes_component_id IS NULL

Posted in SQL Server | Leave a comment

Populate NULL component_ids from related tables

Needed to populate a new column in one table from a semi-related existing table, based on a value in another related table. I do this once in a blue moon and needed a refresher. Thanks, Pinal Dave, for your post with the syntax!

UPDATE [dbo].[ACA_swprg_software_parts]
SET [ACA_swprg_software_parts].mes_component_id=cpn.component_id
FROM [dbo].[ACA_swprg_software_parts] sp
INNER JOIN [dbo].[ACA_swprg_programming_steps] prgstep ON prgstep.[programming_step_id] = sp.[programming_step_id]
INNER JOIN qs_components cpn ON cpn.product_id=prgstep.[mes_product_id] AND cpn.part_number = sp.[part_number]
WHERE sp.mes_component_id IS NULL

Ref: https://blog.sqlauthority.com/2013/04/30/sql-server-update-from-select-statement-using-join-in-update-statement-multiple-tables-in-update-statement/

Posted in SQL Server | Leave a comment