Tárolt eljárás, amely ütemezhető rendszeres ellenőrzésre:
Continue reading “Szabad tárhely az SQL szerveren”
Powershell 6 telepítése
Powershell telepítése:
Első lépésként telepítsük a .Net Framework-ot.
Jelenleg a legutolsó verzió 4.7.1
Majd töltsük le a nyílt forráskódú telepítőt: https://github.com/PowerShell/Powershell
Windows verzió: PowerShell-6.0.2-win-x64.msi
Powershell-t akár linux-ra és macOS-re is telepíthetünk a 6-os verziótól!
Microsoft SQL Server Notes for Professionals
Sql server tippek gyűjteménye haladóknak.
Letöltés: Microsoft SQL Server Notes For Professionals
Ingyenes digitális könyv és szabadon terjeszthető. 🙂
AOAG Synchron Commit time
Átlagos tranzakció végrehajtási ideje:
|
1 2 3 |
select wait_type, waiting_tasks_count, wait_time_ms, wait_time_ms/waiting_tasks_count as 'time_per_wait' from sys.dm_os_wait_stats where waiting_tasks_count >0 and wait_type = 'HADR_SYNC_COMMIT' |
SQL szerver válasz ideje ms-ban:
| wait_type | waiting_tasks_count | wait_time_ms | time_per_wait |
| HADR_SYNC_COMMIT | 10809645 | 135666120 | 12 |
SQL Database downgrade internal version 869 and 852 and Compatibility Level
The database ‘database_name’ cannot be opened because it is version 869. This server supports version 852 and earlier. A downgrade path is not supported.
The internal database versions for SQL Server aren’t clearly documented in the Microsoft documentation:
| SQL Server Version | Internal Database Version | Database Compatibility Level |
|---|---|---|
| SQL Server 2017 | 904 | 150 |
| SQL Server 2017 | 869 | 140 |
| SQL Server 2016 | 852 | 130 |
| SQL Server 2014 | 782 | 120 |
| SQL Server 2012 | 706 | 110 |
| SQL Server 2012 CTP1 (a.k.a. SQL Server 2011 Denali) |
684 | 110 |
| SQL Server 2008 R2 | 660 / 661 | 100 |
| SQL Server 2008 | 655 | 100 |
| SQL Server 2005 SP2+ with VarDecimal enabled |
612 | 90 |
| SQL Server 2005 | 611 | 90 |
| SQL Server 2000 | 539 | 80 |
| SQL Server 7.0 | 515 | 70 |
| SQL Server 6.5 | 408 | 65 |
Performance Monitor for SQL Server
SQL szerver és windows os performancia figyelés:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
<?xml version="1.0" encodINSTANCE="UTF-16"?> <DataCollectorSet> <Status>0</Status> <Duration>1209600</Duration> <Description> </Description> <DescriptionUnresolved> </DescriptionUnresolved> <DisplayName> </DisplayName> <DisplayNameUnresolved> </DisplayNameUnresolved> <SchedulesEnabled>-1</SchedulesEnabled> <LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\SQL07_20170319-000003</LatestOutputLocation> <Name>SQL</Name> <OutputLocation>C:\PerfLogs\Admin\SQL\SQLSRV01_20180307-000001</OutputLocation> <RootPath>%systemdrive%\PerfLogs\Admin\SQL2</RootPath> <Segment>0</Segment> <SegmentMaxDuration>0</SegmentMaxDuration> <SegmentMaxSize>2000</SegmentMaxSize> <SerialNumber>4</SerialNumber> <Server> </Server> <Subdirectory> </Subdirectory> <SubdirectoryFormat>3</SubdirectoryFormat> <SubdirectoryFormatPattern>yyyyMMdd\-NNNNNN</SubdirectoryFormatPattern> <Task> </Task> <TaskRunAsSelf>0</TaskRunAsSelf> <TaskArguments> </TaskArguments> <TaskUserTextArguments> </TaskUserTextArguments> <UserAccount>SYSTEM</UserAccount> <Security></Security> <StopOnCompletion>0</StopOnCompletion> <PerformanceCounterDataCollector> <DataCollectorType>0</DataCollectorType> <Name>DataCollector01</Name> <FileName>SQLdatacollector01</FileName> <FileNameFormat>3</FileNameFormat> <FileNameFormatPattern>yyMMdd</FileNameFormatPattern> <LogAppend>0</LogAppend> <LogCircular>0</LogCircular> <LogOverwrite>0</LogOverwrite> <LatestOutputLocation>C:\PerfLogs\Admin\sql\sqlB\</LatestOutputLocation> <DataSourceName> </DataSourceName> <SampleInterval>5</SampleInterval> <SegmentMaxRecords>0</SegmentMaxRecords> <LogFileFormat>3</LogFileFormat> <Counter>\LogicalDisk(*)\Avg. Disk Queue Length</Counter> <Counter>\Memory\Available MBytes</Counter> <Counter>\Memory\% Committed Bytes In Use</Counter> <Counter>\Memory\Free System Page Table Entries</Counter> <Counter>\Memory\Pages/sec</Counter> <Counter>\Memory\Pool Nonpaged Bytes</Counter> <Counter>\Memory\Pool Paged Bytes</Counter> <Counter>\System\Processor Queue Length</Counter> <Counter>\PhysicalDisk(*)\Avg. Disk sec/Read</Counter> <Counter>\PhysicalDisk(*)\Avg. Disk sec/Write</Counter> <Counter>\PhysicalDisk(*)\Avg. Disk sec/Transfer</Counter> <Counter>\PhysicalDisk(*)\Disk Reads/sec</Counter> <Counter>\PhysicalDisk(*)\Disk Writes/sec</Counter> <Counter>\Network Interface(*)\Bytes Received/sec</Counter> <Counter>\Network Interface(*)\Bytes Sent/sec</Counter> <Counter>\Network Interface(*)\Bytes Total/sec</Counter> <Counter>\Processor(_Total)\% Processor Time</Counter> <Counter>\Processor(_Total)\% Interrupt Time</Counter> <Counter>\Processor(_Total)\% User Time</Counter> <Counter>\MSSQL$INSTANCE:General Statistics\User Connections</Counter> <Counter>\MSSQL$INSTANCE:Memory Manager\Memory Grants PendINSTANCE</Counter> <Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Compilations/sec</Counter> <Counter>\MSSQL$INSTANCE:SQL Statistics\SQL Re-Compilations/sec</Counter> <Counter>\MSSQL$INSTANCE:Buffer Manager\Buffer cache hit ratio</Counter> <Counter>\MSSQL$INSTANCE:Buffer Manager\Page life expectancy</Counter> <Counter>\MSSQL$INSTANCE:Locks(_Total)\Lock Waits/sec</Counter> <Counter>\MSSQL$INSTANCE:Locks(_Total)\Number of Deadlocks/sec</Counter> <Counter>\MSSQL$INSTANCE:Latches\Latch Waits/sec</Counter> <Counter>\MSSQL$INSTANCE:Latches\Total Latch Wait Time (ms)</Counter> <Counter>\MSSQL$INSTANCE:Latches\Average Latch Wait Time (ms)</Counter> <Counter>\MSSQL$INSTANCE:SQL Statistics\Batch Requests/sec</Counter> <Counter>\MSSQL$INSTANCE:Transactions\Longest Transaction RunnINSTANCE Time</Counter> <Counter>\Process(sqlservr)\% Privileged Time</Counter> <Counter>\Process(sqlservr)\% Processor Time</Counter> <Counter>\Process(sqlservr)\% User Time</Counter> <Counter>\Process(sqlservr)\Private Bytes</Counter> <Counter>\Process(sqlservr)\Thread Count</Counter> <Counter>\Process(sqlservr)\Virtual Bytes</Counter> <Counter>\Process(sqlservr)\Handle Count</Counter> </PerformanceCounterDataCollector> <DataManager> <Enabled>0</Enabled> <CheckBeforeRunnINSTANCE>0</CheckBeforeRunnINSTANCE> <MinFreeDisk>0</MinFreeDisk> <MaxSize>0</MaxSize> <MaxFolderCount>0</MaxFolderCount> <ResourcePolicy>0</ResourcePolicy> <ReportFileName>report.html</ReportFileName> <RuleTargetFileName>report.xml</RuleTargetFileName> <EventsFileName> </EventsFileName> </DataManager> </DataCollectorSet> |
Felesleges részek kivétele: pl Network Interface.
Session legutóbbi lekérdezése
Mit futtatott az bejövő user vagy alkalmazás?
|
1 2 3 4 5 6 7 |
SELECT most_recent_session_id, text FROM sys.dm_exec_connections CROSS APPLY sys.dm_exec_sql_text(most_recent_sql_handle) WHERE session_id = (67) -- adott session id vagy a szűrű feltétel kivétele GO |
VMware hot cpu hozzáadás SQL szerverhez futás közben
CPU módosítás történt: vCPU10 -ről vCPU 14 -re SQL AO AG enterprise szerveren.
A túlterhelt vmware host-on 11:22:36 környékén bekövetkezett egy rövid idejű fagyás, alátamasztás: The system time has changed to 2018-04-11T09:22:37.175000000Z from 2018-04-11T09:22:36.337733900Z.
11:22:47 Windows Server Failover Cluster did not receive a process event signal from SQL Server hosting availability group ‘servername’ within the lease timeout period.
11:22:48 -kor cluster szolgáltatás failed. Windows Server Failover Cluster did not receive a process event signal from SQL Server hosting availability group ‘servername’ within the lease timeout period.
11:22:57 4 db vcpu hozzáadás
Processor 13 in group 0 exposes the following power management capabilities:Idle state type: ACPI Idle (C) States (1 state(s))
Performance state type: ACPI Performance (P) / Throttle (T) States
Nominal Frequency (MHz): 3396
Maximum performance percentage: 100
Minimum performance percentage: 100
Minimum throttle percentage: 12
Majd ezután Always On Availability Groups state change állapot következik és a bejövő kapcsolatokat sorra eldobálja.
11:25:45-kor úgy dönt, hogy minden rendben is ismét primary oldal a kiszolgáló, bejönnek a kapcsolatok és elkezd dolgozni a szerver.